What is MySQL? The Database That Powers Most of the Web

WordPress runs 43% of the web. WordPress runs on MySQL. That single chain of facts makes MySQL the most widely deployed relational database in the world by number of running instances.

But the story goes further than WordPress. Facebook built their original social network on MySQL and scaled it to hundreds of millions of users before migrating parts of the infrastructure. YouTube ran on MySQL for years. GitHub ran MySQL until they migrated to Vitess (which is itself a MySQL-compatible sharding layer). Twitter's original infrastructure included MySQL. Wikipedia runs on MySQL. Booking.com runs one of the largest MySQL deployments in the world.

Then Oracle acquired Sun Microsystems in 2010, which came with the MySQL codebase, and the relationship between MySQL and its community became complicated. The core team that originally built MySQL forked it into MariaDB. I have written about the MariaDB vs MySQL situation in detail elsewhere. The short version: MariaDB is a drop-in compatible fork that the original MySQL developers prefer, and for new projects it deserves serious consideration alongside MySQL.

MySQL still exists, still ships, still has active development, and still runs more production databases than any other relational system on the planet. Understanding it means understanding a substantial fraction of all the web infrastructure running right now.

What MySQL Actually Is

MySQL is an open-source relational database management system. The first version was released in 1995 by Michael Widenius (Monty) and David Axmark at MySQL AB. The name comes from Monty's daughter, whose name is My. The dolphin logo is named Sakila.

MySQL implements SQL and stores data in tables with rows and columns. The architecture distinguishes itself from PostgreSQL in one important way: MySQL separates the query parser and optimizer from the storage engine. The storage engine is the component that actually reads and writes data to disk. MySQL supports multiple storage engines and you choose per-table.

InnoDB is the default and the one you use for everything. It supports transactions, foreign keys, row-level locking, and crash recovery through its redo log. Since MySQL 5.5 in 2010, InnoDB has been the default. Any advice you see about MyISAM (the old default) is outdated. Do not use MyISAM for new tables.

MyISAM is the legacy storage engine. No transactions. No foreign keys. Table-level locking means one write blocks all other writes to the entire table. Full-text search was historically its advantage, but InnoDB added full-text indexes in MySQL 5.6. MyISAM exists for backward compatibility only.

MEMORY stores tables entirely in RAM. Fast for reads and writes. Data is lost on server restart. Useful for temporary computation tables and derived datasets.

Archive is optimized for write-heavy, read-rare workloads. It compresses rows on insert and does not support indexes. For append-only log tables where you rarely query old records.

The storage engine abstraction means you can technically mix engines across tables in the same database. Do not do this. Use InnoDB everywhere and move on.

Configuration That Matters

The MySQL configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu) controls server behavior. Most defaults are wrong for production.

[mysqld]
innodb_buffer_pool_size         = 4G
innodb_buffer_pool_instances    = 4
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 64M
innodb_flush_log_at_trx_commit  = 1
innodb_flush_method             = O_DIRECT
innodb_file_per_table           = ON
innodb_io_capacity              = 2000
innodb_io_capacity_max          = 4000
 
max_connections                 = 200
wait_timeout                    = 600
interactive_timeout             = 600
connect_timeout                 = 10
 
server-id                       = 1
log_bin                         = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds      = 604800
max_binlog_size                 = 256M
binlog_format                   = ROW
sync_binlog                     = 1
 
slow_query_log                  = ON
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 1
log_queries_not_using_indexes   = ON
 
character-set-server            = utf8mb4
collation-server                = utf8mb4_unicode_ci
 
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

innodb_buffer_pool_size is the single most important setting. It controls how much RAM InnoDB uses to cache data and indexes. For a dedicated database server, set it to 70-80% of available RAM. A larger buffer pool means more data fits in memory and fewer reads hit disk. The default of 128MB is completely wrong for any production database.

innodb_flush_log_at_trx_commit = 1 flushes the redo log to disk on every transaction commit. This guarantees ACID durability. Setting it to 2 improves write performance at the cost of potentially losing up to one second of transactions if the OS crashes. Setting it to 0 risks losing transactions on a MySQL crash alone. For any data you cannot afford to lose, keep it at 1.

binlog_format = ROW records actual row changes in binary logs rather than the SQL statements. Row-based binary logging is required for reliable replication, particularly for non-deterministic queries using NOW() or RAND().

sql_mode = STRICT_TRANS_TABLES,... enables strict mode. Without this, MySQL silently truncates strings that exceed column length limits, inserts zero dates for invalid date inputs, and performs other coercions that corrupt data without any error. This is MySQL's most notorious gotcha. Always run strict mode. If you are inheriting a legacy MySQL installation without strict mode, every piece of data in every VARCHAR column is potentially truncated and you would never know it.

character-set-server = utf8mb4 deserves its own section.

The utf8 vs utf8mb4 Disaster

MySQL's utf8 character set is broken by design. It only supports 3-byte UTF-8 characters, which excludes all 4-byte characters. The 4-byte characters include emoji and certain Chinese, Japanese, and Korean characters. MySQL's utf8 is not real UTF-8. It is a 3-byte subset of UTF-8 that Monty implemented incorrectly in 2002.

utf8mb4 is real UTF-8. It supports all Unicode characters including emoji. If you create a table or column with CHARACTER SET utf8 in MySQL, inserting emoji either silently truncates to an empty string (without strict mode) or throws an error (with strict mode). Either way, your data is wrong.

The fix for new databases: always use utf8mb4 COLLATE utf8mb4_unicode_ci.

The fix for existing databases:

ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE products CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CONVERT TO CHARACTER SET rewrites every text column in the table. On a large table, this is a blocking operation. Use pt-online-schema-change or gh-ost for zero-downtime migrations on tables with significant traffic.

Also update your application's database connection to specify charset=utf8mb4. In most MySQL client libraries, the connection charset defaults to latin1 or utf8 unless you specify otherwise, which will re-corrupt data even after fixing the schema.

Schema Design

CREATE TABLE users (
    id            BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
    username      VARCHAR(80)       NOT NULL,
    email         VARCHAR(255)      NOT NULL,
    password_hash VARCHAR(255)      NOT NULL,
    is_active     TINYINT(1)        NOT NULL DEFAULT 1,
    created_at    DATETIME(6)       NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at    DATETIME(6)       NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                                    ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_username (username),
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
CREATE TABLE categories (
    id        INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    name      VARCHAR(100)  NOT NULL,
    slug      VARCHAR(100)  NOT NULL,
    parent_id INT UNSIGNED  NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_categories_slug (slug),
    KEY idx_categories_parent (parent_id),
    CONSTRAINT fk_categories_parent
        FOREIGN KEY (parent_id) REFERENCES categories(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
CREATE TABLE products (
    id          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    name        VARCHAR(255)     NOT NULL,
    slug        VARCHAR(255)     NOT NULL,
    description TEXT             NOT NULL,
    price       DECIMAL(10,2)    NOT NULL,
    stock       INT UNSIGNED     NOT NULL DEFAULT 0,
    category_id INT UNSIGNED     NOT NULL,
    owner_id    BIGINT UNSIGNED  NOT NULL,
    status      ENUM('draft','active','archived') NOT NULL DEFAULT 'draft',
    created_at  DATETIME(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at  DATETIME(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                                 ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_products_slug (slug),
    KEY idx_products_status_created (status, created_at),
    KEY idx_products_category_status (category_id, status),
    KEY idx_products_owner (owner_id),
    FULLTEXT KEY ft_products_search (name, description),
    CONSTRAINT fk_products_category
        FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_products_owner
        FOREIGN KEY (owner_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT chk_products_price CHECK (price > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
CREATE TABLE orders (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id         BIGINT UNSIGNED NOT NULL,
    status          ENUM('pending','confirmed','shipped','delivered','cancelled')
                                    NOT NULL DEFAULT 'pending',
    total_amount    DECIMAL(10,2)   NOT NULL,
    shipping_name   VARCHAR(255)    NOT NULL,
    shipping_street VARCHAR(255)    NOT NULL,
    shipping_city   VARCHAR(100)    NOT NULL,
    shipping_country VARCHAR(2)     NOT NULL,
    shipping_postal VARCHAR(20)     NOT NULL,
    notes           TEXT            NULL,
    created_at      DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at      DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                                    ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_orders_user_status (user_id, status),
    KEY idx_orders_status_created (status, created_at),
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
CREATE TABLE order_items (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id   BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity   INT UNSIGNED    NOT NULL,
    unit_price DECIMAL(10,2)   NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_order_items_order_product (order_id, product_id),
    KEY idx_order_items_product (product_id),
    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT chk_order_items_quantity CHECK (quantity > 0),
    CONSTRAINT chk_order_items_price CHECK (unit_price > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

BIGINT UNSIGNED for primary keys. This gives you 18.4 quintillion possible values. INT UNSIGNED gives you 4.3 billion. On a high-volume table processing millions of inserts per day, INT UNSIGNED is exhausted in years. Migrating a primary key column type on a live production table with hundreds of millions of rows is a painful operation. Start with BIGINT UNSIGNED.

DATETIME(6) not TIMESTAMP for time columns. TIMESTAMP has a range problem: it maxes out at January 19, 2038 (the Unix epoch 32-bit overflow). DATETIME stores up to year 9999 and does not have this problem. The (6) gives microsecond precision. Without it, DATETIME stores to second precision only, which loses sub-second timing information.

ENUM for fixed value sets. MySQL stores ENUM values as integers internally. An ENUM with up to 255 values uses 1 byte per row. The caveat: changing the allowed values in an ENUM requires ALTER TABLE, which rewrites the entire table. For value sets that change rarely (order statuses, product statuses), ENUM is appropriate. For value sets that change frequently, use VARCHAR with a CHECK constraint.

CHECK constraints require MySQL 8.0.16+. Before 8.0.16, MySQL parsed CHECK constraints but did not enforce them. They were decorative. If you are running an older MySQL version, CHECK (price > 0) does nothing and you need application-level validation to prevent negative prices from entering the database.

InnoDB Transactions

InnoDB's ACID transaction model is what makes MySQL viable for applications that need data integrity.

START TRANSACTION;
 
SELECT stock
FROM products
WHERE id = 42
FOR UPDATE;
 
UPDATE products
SET stock = stock - 2,
    updated_at = NOW(6)
WHERE id = 42
  AND stock >= 2;
 
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT 1001, 42, 2, price
FROM products
WHERE id = 42;
 
UPDATE orders
SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_id = 1001
),
    updated_at = NOW(6)
WHERE id = 1001;
 
COMMIT;

FOR UPDATE acquires an exclusive lock on the selected rows. No other transaction can modify those rows until this transaction commits or rolls back. This prevents two concurrent orders from both seeing sufficient stock and both decrementing it into negative territory.

MySQL's default isolation level is REPEATABLE READ. Under this level, a transaction sees a consistent snapshot of the database as of its first read. Regular reads within the transaction return the same data regardless of concurrent writes, because MySQL reads from the snapshot.

The critical nuance: SELECT ... FOR UPDATE ignores the snapshot and reads the current committed state. It locks the current row. Within the same transaction, a regular SELECT and a SELECT ... FOR UPDATE on the same row can return different results if another transaction committed between them. This is intentional behavior, not a bug, but it catches people off guard.

InnoDB uses gap locks to prevent phantom reads. This can cause deadlocks in high-concurrency insert workloads. Two transactions each holding a gap lock can deadlock when both try to insert in the locked range. If you see ERROR 1213 (40001): Deadlock found when trying to get lock in high-concurrency workloads, consider changing the isolation level to READ COMMITTED, which uses row locks without gap locks, at the cost of allowing phantom reads.

Indexes and Query Optimization

InnoDB's primary key is a clustered index. The actual table data is stored in primary key order within the B-tree structure. Secondary indexes store the secondary key value plus the primary key value (not a disk pointer). A secondary index lookup requires two B-tree traversals: one for the secondary index, one for the clustered index to fetch the actual row data.

A covering index avoids the second traversal entirely. If all columns your query needs are in the index, MySQL satisfies the query from the index alone.

CREATE INDEX idx_products_status_created ON products(status, created_at DESC);
 
SELECT id, name, price, created_at
FROM products
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20;
 
CREATE INDEX idx_products_status_cover
ON products(status, created_at DESC, id, name, price);

The second index includes id, name, price, and created_at. The query needs only those columns. MySQL reads everything it needs from the index without touching the clustered index. Using index in the Extra column of EXPLAIN output confirms a covering index is in use.

Column order in compound indexes matters strictly. The leftmost columns filter first. (status, created_at) efficiently covers queries filtering on status alone, or on both status and created_at. It does not help queries filtering only on created_at. Design indexes around your most frequent query patterns, verified with EXPLAIN.

EXPLAIN FORMAT=JSON
SELECT p.id, p.name, p.price, c.name AS category_name
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.status = 'active'
  AND p.category_id = 5
ORDER BY p.created_at DESC
LIMIT 20\G

Key fields to read in EXPLAIN output:

Using filesort means the ORDER BY could not use an index and MySQL is sorting in memory or on disk. On a table with millions of rows, a filesort is slow. Create an index that covers both the WHERE filter columns and the ORDER BY columns.

Full-Text Search

MySQL's built-in full-text search handles small to medium content volumes without the operational overhead of Elasticsearch.

SELECT
    id,
    name,
    price,
    MATCH(name, description) AGAINST('wireless headphones' IN NATURAL LANGUAGE MODE) AS relevance
FROM products
WHERE MATCH(name, description) AGAINST('wireless headphones' IN NATURAL LANGUAGE MODE)
  AND status = 'active'
ORDER BY relevance DESC
LIMIT 20;
 
SELECT id, name, price
FROM products
WHERE MATCH(name, description)
      AGAINST('+wireless +headphones -wired' IN BOOLEAN MODE)
  AND status = 'active'
ORDER BY price ASC;
 
SELECT id, name, price
FROM products
WHERE MATCH(name, description)
      AGAINST('"noise cancelling" headphones' IN BOOLEAN MODE)
  AND status = 'active';

IN NATURAL LANGUAGE MODE ranks results by relevance (TF-IDF weighting). IN BOOLEAN MODE supports + (required), - (excluded), and "..." (exact phrase) operators but does not rank by relevance.

The MATCH()...AGAINST() expression in both the SELECT list and WHERE clause executes the search once and reuses the result. The minimum indexed word length is 3 characters by default. Words shorter than this are not indexed. Stop words (the, a, is, etc.) are excluded.

For production search features needing autocomplete, facets, typo tolerance, and ranking control, Elasticsearch is the right tool. MySQL full-text search is appropriate for simple keyword search in smaller datasets where operational simplicity matters more than search sophistication.

Analytics Queries

SELECT
    DATE_FORMAT(o.created_at, '%Y-%m')  AS month,
    COUNT(DISTINCT o.id)                AS orders,
    COUNT(DISTINCT o.user_id)           AS customers,
    SUM(o.total_amount)                 AS revenue,
    AVG(o.total_amount)                 AS avg_order_value,
    SUM(oi.quantity)                    AS units_sold
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'delivered'
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY month;
 
SELECT
    p.name,
    p.price,
    SUM(oi.quantity)                    AS total_sold,
    SUM(oi.quantity * oi.unit_price)    AS total_revenue,
    COUNT(DISTINCT oi.order_id)         AS order_count
FROM products p
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'delivered'
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY p.id, p.name, p.price
ORDER BY total_revenue DESC
LIMIT 10;
 
SELECT
    u.username,
    COUNT(DISTINCT o.id)    AS order_count,
    SUM(o.total_amount)     AS lifetime_value,
    MAX(o.created_at)       AS last_order,
    DATEDIFF(NOW(), MAX(o.created_at)) AS days_since_last_order
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'delivered'
GROUP BY u.id, u.username
HAVING lifetime_value > 500
ORDER BY lifetime_value DESC
LIMIT 20;

MySQL supports window functions since version 8.0. Before 8.0, the workaround for running totals and row numbering was variable-based tricks that were fragile and non-obvious. If you are still running MySQL 5.7, these patterns require workarounds.

SELECT
    DATE_FORMAT(created_at, '%Y-%m')                                        AS month,
    SUM(total_amount)                                                       AS monthly_revenue,
    SUM(SUM(total_amount)) OVER (ORDER BY DATE_FORMAT(created_at, '%Y-%m')) AS cumulative_revenue,
    RANK() OVER (ORDER BY SUM(total_amount) DESC)                           AS revenue_rank
FROM orders
WHERE status = 'delivered'
  AND created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

SUM(SUM(total_amount)) OVER (ORDER BY ...) is a window function operating on grouped results. The outer SUM is the window function accumulating the running total. The inner SUM is the group aggregate. MySQL 8.0 handles this correctly. MySQL 5.7 does not support this syntax.

Connecting from Node.js

Since the API articles in this series use TypeScript, here is production MySQL access from Node.js with the mysql2 driver:

import mysql, { Pool, PoolConnection, RowDataPacket, ResultSetHeader } from 'mysql2/promise';
 
const pool: Pool = mysql.createPool({
    host:               process.env.DB_HOST     ?? 'localhost',
    port:               Number(process.env.DB_PORT ?? 3306),
    user:               process.env.DB_USER     ?? 'root',
    password:           process.env.DB_PASSWORD ?? '',
    database:           process.env.DB_NAME     ?? 'myapp',
    waitForConnections: true,
    connectionLimit:    20,
    queueLimit:         0,
    charset:            'utf8mb4',
    timezone:           'Z',
    decimalNumbers:     true,
});
 
interface Product extends RowDataPacket {
    id:          number;
    name:        string;
    slug:        string;
    price:       number;
    stock:       number;
    status:      string;
    category_id: number;
    created_at:  Date;
}
 
async function getProducts(
    status:     string = 'active',
    categoryId: number | null = null,
    page:       number = 1,
    perPage:    number = 20
): Promise<{ data: Product[]; total: number }> {
    const offset = (page - 1) * perPage;
    const params: (string | number)[] = [status];
    let where = 'WHERE p.status = ?';
 
    if (categoryId) {
        where += ' AND p.category_id = ?';
        params.push(categoryId);
    }
 
    const [[{ total }]] = await pool.query<RowDataPacket[]>(
        `SELECT COUNT(*) AS total FROM products p ${where}`,
        params
    );
 
    const [rows] = await pool.query<Product[]>(
        `SELECT p.id, p.name, p.slug, p.price, p.stock, p.status,
                p.category_id, p.created_at
         FROM products p ${where}
         ORDER BY p.created_at DESC
         LIMIT ? OFFSET ?`,
        [...params, perPage, offset]
    );
 
    return { data: rows, total: total as number };
}
 
async function createOrder(
    userId:  number,
    items:   Array<{ productId: number; quantity: number }>,
    shipping: Record<string, string>
): Promise<number> {
    const conn: PoolConnection = await pool.getConnection();
 
    try {
        await conn.beginTransaction();
 
        for (const item of items) {
            const [rows] = await conn.query<RowDataPacket[]>(
                'SELECT id, stock, price FROM products WHERE id = ? AND status = "active" FOR UPDATE',
                [item.productId]
            );
 
            if (!rows.length) throw new Error(`Product ${item.productId} not found`);
            if (rows[0].stock < item.quantity) {
                throw new Error(
                    `Insufficient stock for ${item.productId}. ` +
                    `Available: ${rows[0].stock}, requested: ${item.quantity}`
                );
            }
        }
 
        const [orderResult] = await conn.query<ResultSetHeader>(
            `INSERT INTO orders
             (user_id, status, total_amount, shipping_name, shipping_street, shipping_city, shipping_country, shipping_postal)
             VALUES (?, 'pending', 0, ?, ?, ?, ?, ?)`,
            [userId, shipping.name, shipping.street, shipping.city, shipping.country, shipping.postal]
        );
 
        const orderId = orderResult.insertId;
        let total = 0;
 
        for (const item of items) {
            const [[product]] = await conn.query<RowDataPacket[]>(
                'SELECT price FROM products WHERE id = ?',
                [item.productId]
            );
            const unitPrice = product.price as number;
            total += unitPrice * item.quantity;
 
            await conn.query(
                'INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)',
                [orderId, item.productId, item.quantity, unitPrice]
            );
 
            const [updated] = await conn.query<ResultSetHeader>(
                'UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?',
                [item.quantity, item.productId, item.quantity]
            );
 
            if (updated.affectedRows === 0) {
                throw new Error(`Stock depleted for product ${item.productId} during checkout`);
            }
        }
 
        await conn.query(
            'UPDATE orders SET total_amount = ? WHERE id = ?',
            [total, orderId]
        );
 
        await conn.commit();
        return orderId;
 
    } catch (err) {
        await conn.rollback();
        throw err;
    } finally {
        conn.release();
    }
}
 
process.on('SIGTERM', async () => {
    await pool.end();
});

pool.getConnection() acquires a connection from the pool. conn.release() in the finally block returns it whether the transaction succeeded or failed. The AND stock >= ? condition in the UPDATE is a second guard: even if the FOR UPDATE lock passed and the initial stock check passed, another transaction could have decremented stock between those steps. affectedRows === 0 catches that race condition and rolls back.

timezone: 'Z' ensures MySQL interprets DATETIME values as UTC. Without this, your driver uses the server's local timezone, which causes silent time offset bugs when your application server and database server are in different timezones or after daylight saving time transitions.

Production: Replication and ProxySQL

A single MySQL server is a single point of failure. Production deployments run at minimum one primary and one replica.

MySQL binary log replication: the primary writes every committed transaction to the binary log. Replicas connect to the primary, stream the binary log, and apply the same changes in order. Replication is asynchronous by default, meaning the primary does not wait for replicas to apply changes before acknowledging commits.

ProxySQL is the connection proxy that sits between your application and MySQL servers. It provides connection pooling, query routing, and automatic read/write splitting:

[proxysql]
datadir="/var/lib/proxysql"
 
[mysql_variables]
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
default_schema="information_schema"
stacksize=1048576
server_version="8.0.27"
connect_timeout_server=3000
monitor_username="proxysql"
monitor_password="proxysql"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10

With ProxySQL routing rules, SELECT ... FOR UPDATE statements route to the primary (they need to lock rows). Other SELECT statements route to replicas. All writes go to the primary. Your application code does not change. The connection string points to ProxySQL, which handles the routing.

Performance Troubleshooting

Production MySQL performance problems fall into a small number of categories. Knowing which category you are in determines the fix.

Missing indexes. Enable the slow query log and log_queries_not_using_indexes. Any query appearing in that log without an index is a candidate for an index. Run EXPLAIN on it, look for type: ALL or type: index, create the right index, verify with EXPLAIN again.

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;
 
SHOW VARIABLES LIKE 'slow_query_log_file';

Percona's pt-query-digest analyzes slow query log files and groups queries by pattern, showing total execution time, average time, and frequency. This surfaces the highest-impact queries to optimize first.

pt-query-digest /var/log/mysql/slow.log \
    --limit 10 \
    --report-format profile \
    > slow_query_report.txt

Buffer pool pressure. Check the InnoDB buffer pool hit rate:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
 
SELECT
    ROUND(
        (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100,
        2
    ) AS buffer_pool_hit_rate_pct
FROM (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) reads,
(
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) requests;

A hit rate below 95% means the buffer pool is too small. More reads are going to disk than should be. Increase innodb_buffer_pool_size.

Table lock contention. On tables with significant write volume, check for lock waits:

SELECT
    r.trx_id                     AS waiting_trx_id,
    r.trx_mysql_thread_id        AS waiting_thread,
    r.trx_query                  AS waiting_query,
    b.trx_id                     AS blocking_trx_id,
    b.trx_mysql_thread_id        AS blocking_thread,
    b.trx_query                  AS blocking_query,
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
ORDER BY wait_seconds DESC;

Long-running transactions holding locks block other transactions from progressing. Find the blocking query, understand why it is long-running (missing index, application bug, large data set), and fix the root cause.

Deadlocks. InnoDB logs the most recent deadlock to the InnoDB status:

SHOW ENGINE INNODB STATUS\G

Look for the LATEST DETECTED DEADLOCK section. It shows the two transactions that deadlocked, the locks they held, and the locks they were waiting for. Deadlocks are usually caused by transactions acquiring locks in inconsistent orders. Fix by ensuring all transactions that access the same rows do so in the same order, or by using SELECT ... FOR UPDATE to acquire locks upfront rather than acquiring them incrementally as the transaction progresses.

The Percona and MariaDB Ecosystem

MySQL's production operations ecosystem extends well beyond the MySQL binary itself.

Percona Server for MySQL is a drop-in replacement for MySQL Community Edition with additional performance improvements, diagnostic tools, and features backported from enterprise MySQL or developed independently. The Percona Toolkit (pt-query-digest, pt-online-schema-change, pt-table-checksum, pt-slave-delay) is the standard toolkit for MySQL operations regardless of which MySQL distribution you run.

XtraBackup (Percona) is the standard tool for hot backups of InnoDB. Unlike mysqldump, which locks tables or produces a dump while the server is under load, XtraBackup copies InnoDB data files while the server is running, then applies the InnoDB redo log to make the backup consistent. For databases larger than a few hundred megabytes, mysqldump is too slow for production backups.

xtrabackup --backup --target-dir=/backup/full/$(date +%Y%m%d)
xtrabackup --prepare --target-dir=/backup/full/20240115
xtrabackup --copy-back --target-dir=/backup/full/20240115

Orchestrator is the MySQL topology management tool. It visualizes your replication topology, detects primary failures, and performs automatic failover by promoting a replica to primary. Without a tool like Orchestrator, a primary server failure requires manual intervention: identifying which replica is most up-to-date, promoting it, pointing all other replicas at the new primary, and updating your application's database connection string.

The combination of ProxySQL for connection routing and Orchestrator for failover is the standard production MySQL high-availability setup at organizations that run MySQL at scale.

Common MySQL Gotchas

Silent truncation without strict mode. Without STRICT_TRANS_TABLES, inserting a 300-character string into a VARCHAR(255) silently truncates to 255 characters. No error. Your data is corrupted and your application never knew. Always run strict mode.

DATETIME vs TIMESTAMP and the year 2038 problem. TIMESTAMP stores values internally as Unix timestamps (32-bit integer). Values after January 19, 2038 overflow and wrap to zero or produce errors depending on the version. Any TIMESTAMP column in a database built before ~2018 that will be in use after 2038 is a ticking time bomb. Migrate to DATETIME(6).

Integer overflow on AUTO_INCREMENT. An INT primary key maxes at 2.1 billion (signed) or 4.3 billion (unsigned). On any table with significant volume, plan for BIGINT UNSIGNED from the start.

Online schema changes block writes. ALTER TABLE products ADD COLUMN metadata JSON NULL on a table with 50 million rows acquires a metadata lock and rewrites the entire table. This blocks writes for minutes. In production, use pt-online-schema-change from Percona Toolkit or gh-ost from GitHub:

gh-ost \
    --user="root" --password="password" \
    --host=127.0.0.1 \
    --database="myapp" --table="products" \
    --alter="ADD COLUMN metadata JSON NULL" \
    --execute

gh-ost creates a shadow table, copies rows in batches, applies concurrent changes via binary log streaming, and performs an atomic table swap. Writes continue throughout. The final lock is milliseconds, not minutes.

EXPLAIN before shipping any query against large tables. One missing index on a 10-million-row table that is queried 100 times per second is the most common cause of MySQL performance emergencies. Run EXPLAIN on every non-trivial query during development. Using filesort and type: ALL are your warning signs.

MySQL vs PostgreSQL

MySQL and PostgreSQL are both excellent relational databases. The choice between them is less consequential than it used to be.

MySQL's advantages: easier to set up for small deployments, better horizontal read scaling through mature replication tooling, wider compatibility with shared hosting and CMS platforms (WordPress, Drupal, Magento), and marginally faster for simple highly-optimized read queries.

PostgreSQL's advantages: richer data types (arrays, ranges, JSONB with indexing, network types, UUID natively), more powerful query features (recursive CTEs, lateral joins, better window functions), stricter SQL standards compliance, more reliable constraint enforcement historically, and JSONB that eliminates the need for a separate document database for most use cases.

For greenfield projects where you have a free choice, PostgreSQL is my recommendation. The richer type system and better standards compliance catch more data integrity problems at the database layer. The JSONB support is genuinely useful for semi-structured data.

For projects that need to run on shared hosting, integrate with WordPress or similar CMS platforms, or join an existing MySQL infrastructure: MySQL is a completely production-capable choice. The thousands of large-scale production deployments prove it handles real traffic reliably.

The Oracle ownership concern is real. The MariaDB article covers that situation in detail. For anyone concerned about Oracle's long-term MySQL roadmap, MariaDB is a drop-in compatible alternative that requires only a connection string change to switch.

MySQL JSON Support

MySQL 5.7 introduced the JSON data type. MySQL 8.0 expanded it significantly. For columns that store semi-structured data with variable attributes, JSON avoids the EAV anti-pattern without requiring a separate document database.

ALTER TABLE products ADD COLUMN metadata JSON NULL;
 
UPDATE products
SET metadata = JSON_OBJECT(
    'brand',      'AudioTech',
    'color',      'Black',
    'warranty',   '2 years',
    'certifications', JSON_ARRAY('CE', 'FCC', 'RoHS')
)
WHERE id = 42;
 
SELECT
    id,
    name,
    metadata->>'$.brand'   AS brand,
    metadata->>'$.color'   AS color,
    JSON_LENGTH(metadata->'$.certifications') AS cert_count
FROM products
WHERE metadata->>'$.brand' = 'AudioTech'
  AND status = 'active';
 
ALTER TABLE products ADD INDEX idx_products_brand
    ((CAST(metadata->>'$.brand' AS CHAR(100))));
 
SELECT id, name, metadata->>'$.brand' AS brand
FROM products
WHERE CAST(metadata->>'$.brand' AS CHAR(100)) = 'AudioTech'
  AND status = 'active';

metadata->>'$.brand' extracts the brand field from the JSON as an unquoted string. metadata->'$.brand' extracts it as a quoted JSON value. The functional index ((CAST(metadata->>'$.brand' AS CHAR(100)))) creates an index on a computed expression, making JSON field queries index-scannable rather than requiring a full table scan.

MySQL's JSON support is less capable than PostgreSQL's JSONB — no full-text indexing of JSON content, more limited query operators — but it covers the common cases of storing variable product attributes, feature flags, and configuration data without requiring a schema migration every time the attribute set changes.