/mysql-patterns

name: mysql-patterns
description: MySQL and MariaDB schema, query, indexing, transaction, replication, and connection-pool patterns for production backends. Use when designing MySQL or MariaDB schemas and indexes, or when a query, transaction, or replica lags.
metadata:
  origin: ECC

MySQL Patterns

Use this skill when working on MySQL or MariaDB schema design, migrations, slow-query investigation, queue-style transactions, connection pools, or production database configuration. Prefer exact version checks before applying a feature-specific pattern because MySQL and MariaDB have diverged in several SQL details.

Activation

• Designing MySQL or MariaDB tables, indexes, and constraints
• Reviewing migrations before they run on large production tables
• Debugging slow queries, lock waits, deadlocks, or connection exhaustion
• Adding keyset pagination, upserts, full-text search, JSON columns, or queues
• Configuring application connection pools, read replicas, TLS, or slow logs

Version Check

Start by identifying the engine and version:

SELECT VERSION();
SHOW VARIABLES LIKE 'version_comment';

Keep MySQL and MariaDB guidance separate when syntax differs:

• MySQL documents row aliases as the replacement for `VALUES(col)` in

`ON DUPLICATE KEY UPDATE`; `VALUES(col)` is deprecated there.

• MariaDB documents `VALUES(col)` as the supported way to reference inserted

values in `ON DUPLICATE KEY UPDATE`; use it for cross-engine compatibility.

• `SKIP LOCKED` is appropriate for queue-like work only. It skips locked rows

and can return an inconsistent view, so do not use it for general accounting or integrity-sensitive reads.

Schema Defaults

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(32) NOT NULL,
    total DECIMAL(15, 2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_orders_account_status_created (account_id, status, created_at),
    KEY idx_orders_active (account_id, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Default choices:

| Use Case | Prefer | Avoid | | --- | --- | --- | | Surrogate primary keys | `BIGINT UNSIGNED AUTO_INCREMENT` | `INT` for tables that can grow beyond 2B rows | | UUID lookup keys | `BINARY(16)` with conversion helpers | `VARCHAR(36)` primary keys on hot tables | | Money and exact quantities | `DECIMAL(p, s)` | `FLOAT` or `DOUBLE` | | User-facing text | `utf8mb4` tables and indexes | MySQL `utf8` / `utf8mb3` defaults | | Application timestamps | `DATETIME` with UTC managed by the app | Assuming `DATETIME` stores time zone metadata | | Soft deletes | `deleted_at DATETIME NULL` plus scoped indexes | Filtering soft-deleted rows without an index | | Extensible status values | lookup table or constrained `VARCHAR` | `ENUM` when values change often |

Indexing

Composite index order usually follows equality predicates first, then range or sort columns:

CREATE INDEX idx_orders_account_status_created
    ON orders (account_id, status, created_at);

SELECT id, total
FROM orders
WHERE account_id = ?
  AND status = 'pending'
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 50;

Use `EXPLAIN` before adding or changing an index:

EXPLAIN
SELECT id, total
FROM orders
WHERE account_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Signals to investigate:

| Field | Risk Signal | | --- | --- | | `type` | `ALL` on a large table | | `key` | `NULL` when a selective predicate exists | | `rows` | Very high row estimate for an interactive path | | `Extra` | `Using temporary`, `Using filesort`, or broad `Using where` |

Avoid adding indexes blindly. Each index increases write cost, migration time, backup size, and buffer-pool pressure.

Query Patterns

Upsert

Cross-engine-compatible form:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at = CURRENT_TIMESTAMP;

MySQL row-alias form:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
    setting_value = new.setting_value,
    updated_at = CURRENT_TIMESTAMP;

Use the row-alias form only after confirming the target is MySQL. Use `VALUES(col)` for MariaDB or mixed MySQL/MariaDB fleets.

Keyset Pagination

SELECT id, name, created_at
FROM products
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Back it with an index that matches the cursor:

CREATE INDEX idx_products_created_id ON products (created_at, id);

Do not use deep `OFFSET` pagination on large tables; it makes the server scan and discard rows before returning the page.

JSON Fields

Use JSON columns for extension data, not for fields that need heavy relational filtering or constraints.

CREATE TABLE events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL,
    event_type VARCHAR(64)
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED,
    KEY idx_events_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

For frequently queried JSON paths, expose a generated column and index that column. Keep foreign keys, ownership, tenancy, and lifecycle fields relational.

Full-Text Search

ALTER TABLE articles ADD FULLTEXT KEY ft_articles_title_body (title, body);

SELECT id, title, MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

Use external search when you need typo tolerance, complex ranking, cross-table facets, or language-specific analysi