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 analysis beyond built-in full-text behavior.

Transactions

Keep transactions short and lock rows in a consistent order:

START TRANSACTION;

SELECT id, balance

FROM accounts

WHERE id IN (?, ?)

ORDER BY id

FOR UPDATE;

UPDATE accounts SET balance = balance - ? WHERE id = ?;

UPDATE accounts SET balance = balance + ? WHERE id = ?;

COMMIT;

Deadlock and lock-wait checklist:

• Lock rows in a deterministic order across code paths.

• Do external API calls before opening the transaction, not inside it.

• Add indexes for predicates used in UPDATE, DELETE, and locking reads.

• On deadlock, roll back and retry the whole transaction with a bounded retry

budget.

• Capture SHOW ENGINE INNODB STATUS\G soon after a deadlock; it is overwritten

by later events.

Queue-style worker claim:

START TRANSACTION;

SELECT id

FROM jobs

WHERE status = 'pending'

ORDER BY created_at

LIMIT 1

FOR UPDATE SKIP LOCKED;

UPDATE jobs

SET status = 'processing', started_at = CURRENT_TIMESTAMP

WHERE id = ?;

COMMIT;

Use SKIP LOCKED only for queue-like workloads where skipping a locked row is

acceptable. It is not a replacement for normal transactional consistency.

Connection Pools

SQLAlchemy example:

from sqlalchemy import create_engine

engine = create_engine(

    "mysql+mysqlconnector://app:secret@db.internal/app",

    pool_size=10,

    max_overflow=5,

    pool_timeout=30,

    pool_recycle=240,

    pool_pre_ping=True,

    connect_args={"connect_timeout": 5},

)

Node.js mysql2 example:

import mysql from 'mysql2/promise';

const pool = mysql.createPool({

  host: process.env.DB_HOST,

  user: process.env.DB_USER,

  password: process.env.DB_PASSWORD,

  database: process.env.DB_NAME,

  waitForConnections: true,

  connectionLimit: 10,

  queueLimit: 0,

  enableKeepAlive: true,

  keepAliveInitialDelay: 30000,

});

const [rows] = await pool.execute(

  'SELECT id, total FROM orders WHERE account_id = ? LIMIT 50',

  [accountId],

);

Keep application pool recycling below the server wait_timeout. If the server

uses wait_timeout = 300, a pool_recycle around 240 seconds is coherent;

pool_pre_ping still helps recover from network and failover events.

Diagnostics

Useful first-pass commands:

SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G;

SHOW VARIABLES LIKE 'slow_query_log';

SHOW VARIABLES LIKE 'long_query_time';

Enable the slow log in a controlled environment:

SET GLOBAL slow_query_log = 'ON';

SET GLOBAL long_query_time = 1;

SET GLOBAL log_queries_not_using_indexes = 'ON';

Use EXPLAIN ANALYZE only when it is safe to execute the query. It runs the

statement and can be expensive on production-sized data.

Replication

Read replicas can lag. Do not route read-your-own-write paths, checkout flows,

permission checks, or idempotency-key reads to a replica immediately after a

write.

-- MySQL legacy terminology, still common in existing fleets

SHOW SLAVE STATUS\G;

-- Newer terminology where supported

SHOW REPLICA STATUS\G;

Check the engine/version before standardizing on one command. Monitor replica

SQL thread health, IO thread health, and lag, not just whether the TCP

connection is alive.

Security

CREATE USER 'app'@'%' IDENTIFIED BY 'use-a-secret-manager';

GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';

ALTER USER 'app'@'%' REQUIRE SSL;

SELECT user, host

FROM mysql.user

WHERE user = '';

DROP USER IF EXISTS ''@'localhost';

DROP USER IF EXISTS ''@'%';

Security review points:

• Do not grant ALL PRIVILEGES or *.* to application users.

• Require TLS for application users when traffic crosses hosts or networks.

• Store credentials in the platform secret manager, not in examples, scripts, or

repository files.

• Separate migration/admin users from runtime application users.

• Audit public network exposure and bind addresses before tuning performance.

Configuration

Example starting point for a dedicated database host:

[mysqld]

innodb_buffer_pool_size = 4G

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

max_connections = 300

thread_cache_size = 50

wait_timeout = 300

interactive_timeout = 300

innodb_lock_wait_timeout = 10

slow_query_log = ON

long_query_time = 1

log_queries_not_using_indexes = ON

log_bin = mysql-bin

binlog_format = ROW

binlog_expire_logs_seconds = 604800

Treat configuration values as a prompt for review, not a universal preset. Size

memory, connections, log retention, and durability settings from workload,

hardware, backup policy, and recovery objectives.

Anti-Patterns

Anti-Pattern

Risk

Better Pattern

SELECT * in hot paths

Over-fetching and brittle clients

Select explicit columns

Deep OFFSET pagination

Linear scans and slow pages

Keyset pagination

No index on foreign-key joins

Slow joins and lock-heavy deletes

Index FK columns intentionally

Long transactions

Lock waits and large undo history

Commit small units of work

Direct DML against mysql.user

Grant-table corruption risk

Use CREATE USER, ALTER USER, DROP USER

Application user with admin grants

High blast radius

Least-privilege runtime user

Pool recycle above wait_timeout

Stale pooled connections

Recycle below timeout and pre-ping

Replica reads after writes

Stale user-facing state

Pin read-after-write flows to primary

Output Expectations

When this skill is used for review, return:

• Engine/version assumptions.

• Highest-risk correctness, lock, security, and migration issues.

• Exact SQL or code changes for the safe path.

• Validation plan: EXPLAIN, migration dry run, lock/deadlock check, and

rollback criteria.

• Any MySQL/MariaDB syntax differences that affect the recommendation.

Related

• Skill: postgres-patterns - PostgreSQL-specific schema and query patterns

• Skill: database-migrations - migration planning and rollout safety

• Skill: backend-patterns - API and service-layer patterns

• Skill: security-review - secret handling, auth, and least privilege

• Agent: database-reviewer - broader database review workflow