MySQL

Use this skill to make safe, measurable MySQL/InnoDB changes.

Workflow

• Define workload and constraints (read/write mix, latency target, data volume, MySQL version, hosting platform).

• Read only the relevant reference files linked in each section below.

• Propose the smallest change that can solve the problem, including trade-offs.

• Validate with evidence (EXPLAIN , EXPLAIN ANALYZE , lock/connection metrics, and production-safe rollout steps).

• For production changes, include rollback and post-deploy verification.

Schema Design

• Prefer narrow, monotonic PKs (BIGINT UNSIGNED AUTO_INCREMENT ) for write-heavy OLTP tables.

• Avoid random UUID values as clustered PKs; if external IDs are required, keep UUID in a secondary unique column.

• Always utf8mb4 / utf8mb4_0900_ai_ci . Prefer NOT NULL , DATETIME over TIMESTAMP .

• Lookup tables over ENUM . Normalize to 3NF; denormalize only for measured hot paths.

References:

• primary-keys

• data-types

• character-sets

• json-column-patterns

Indexing

• Composite order: equality first, then range/sort (leftmost prefix rule).

• Range predicates stop index usage for subsequent columns.

• Secondary indexes include PK implicitly. Prefix indexes for long strings.

• Audit via performance_schema — drop indexes with count_read = 0 .

References:

• composite-indexes

• covering-indexes

• fulltext-indexes

• index-maintenance

Partitioning

• Partition time-series (>50M rows) or large tables (>100M rows). Plan early — retrofit = full rebuild.

• Include partition column in every unique/PK. Always add a MAXVALUE catch-all.

References:

• partitioning

Query Optimization

• Check EXPLAIN — red flags: type: ALL , Using filesort , Using temporary .

• Cursor pagination, not OFFSET . Avoid functions on indexed columns in WHERE .

• Batch inserts (500–5000 rows). UNION ALL over UNION when dedup unnecessary.

References:

• explain-analysis

• query-optimization-pitfalls

• n-plus-one

Transactions & Locking

• Default: REPEATABLE READ (gap locks). Use READ COMMITTED for high contention.

• Consistent row access order prevents deadlocks. Retry error 1213 with backoff.

• Do I/O outside transactions. Use SELECT ... FOR UPDATE sparingly.

References:

• isolation-levels

• deadlocks

• row-locking-gotchas

Operations

• Use online DDL (ALGORITHM=INPLACE ) when possible; test on replicas first.

• Tune connection pooling — avoid max_connections exhaustion under load.

• Monitor replication lag; avoid stale reads from replicas during writes.

References:

• online-ddl

• connection-management

• replication-lag

Guardrails

• Prefer measured evidence over blanket rules of thumb.

• Note MySQL-version-specific behavior when giving advice.

• Ask for explicit human approval before destructive data operations (drops/deletes/truncates).