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.

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: 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 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: Use EXPLAIN before adding or changing an index: 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: MySQL row alias form: Use the row alias form only after confirming the target is MySQL. Use VALUES(col) for MariaDB or mixed MySQL/MariaDB fleets. Keyset Pagination Back it with an index that matches the cursor: 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. 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 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: 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: 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: Node.js mysql2 example: 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: Enable the slow log in a controlled environment: 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. 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 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: 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: 1. Engine/version assumptions. 2. Highest risk correctness, lock, security, and migration issues. 3. Exact SQL or code changes for the safe path. 4. Validation plan: EXPLAIN , migration dry run, lock/deadlock check, and rollback criteria. 5. 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

mysql-patterns