Mysql

MySQL / MariaDB engine

You are working below portable SQL, at the layer where the answer depends on which engine is running. This skill owns MySQL 8.4 LTS and MariaDB 11.8 LTS: the InnoDB clustered-index storage model, MySQL-flavoured DDL and types, index design and the leftmost-prefix rule, reading EXPLAIN and fixing the plan, online DDL, replication, locking/deadlocks, and day-2 server config.

The dividing line is simple: if the answer is identical on PostgreSQL, it belongs in sql, not here. sql owns the dialect-independent SELECT grammar. mysql owns how this engine stores, plans, locks, and replicates. postgresdb is the peer engine for the other database — same body shape, different facts, never the same answer.

When to use

• Designing or reviewing a MySQL/MariaDB schema: engine choice, integer/DECIMAL/VARCHAR sizing, utf8mb4 charset/collation, JSON + generated/STORED columns, PK design for InnoDB.
• A query is slow or scans too many rows; reading EXPLAIN / EXPLAIN ANALYZE / FORMAT=JSON.
• Choosing or adding an index: composite column order, covering indexes, prefix indexes on TEXT, invisible indexes for safe rollout, why an index is not used.
• Schema change on a large/hot table without downtime: ALGORITHM=INSTANT/INPLACE/COPY, pt-osc, gh-ost.
• Replication: binlog row format, GTID (incl. tagged GTIDs), replica lag, semi-sync, group replication.
• Locking/concurrency: deadlocks, gap/next-key locks, REPEATABLE READ, SELECT ... FOR UPDATE.
• Operating the server: buffer pool, caching_sha2_password + TLS, slow-query log, performance_schema.
• Migrating 5.7/8.0 → 8.4 LTS, or reasoning about MySQL ↔ MariaDB divergence.

When NOT to use

The boundaries with planetscale and db-migrations are sharp: this skill owns the raw-MySQL mechanics (EXPLAIN, index choice, ALGORITHM=, gh-ost). PlanetScale wraps those in its platform workflow; db-migrations wraps them in vendor-neutral strategy. You own the knobs they ride on.

Pick your version first

Get this wrong and every later decision (auth, vector, isolation defaults) is wrong too.

VECTOR is a MySQL 9.0 (Innovation) feature, not in 8.4 LTS. MariaDB 11.8 also has VECTOR but with different functions (VEC_DISTANCE_COSINE() vs MySQL's STRING_TO_VECTOR()) — see references/mysql-vs-mariadb.md. Do not assume one's vector SQL runs on the other.

Non-negotiables

1. utf8mb4, always — at the column level. Legacy utf8 (alias utf8mb3) is 3-byte and silently truncates emoji and supplementary characters. Default collation is utf8mb4_0900_ai_ci. Setting it on the connection only is not enough; set it on the column.
2. Small monotonic PRIMARY KEY. An InnoDB table is its PK B-tree, and every secondary index stores the PK as its row pointer. A random UUID/CHAR(36) PK bloats every secondary index and wrecks insert locality. Use BIGINT AUTO_INCREMENT or an ordered UUIDv7 stored as BINARY(16).
3. binlog_format=ROW + GTID. ROW is the only reliable replication format; GTID gives each transaction a globally unique id with auto-skip so it applies at most once per replica.
4. caching_sha2_password + TLS. It is the default auth plugin and SHA-256 based; clients need TLS for first-time auth. mysql_native_password is disabled by default in 8.4 and gone in 9.0 — do not design around it.
5. Index column order follows the leftmost prefix. INDEX (a,b,c) serves a, a,b, a,b,c — never b alone. Put equality columns first, then the range/ORDER BY column.
6. Never ALTER a hot table without choosing an algorithm. Default COPY locks and rebuilds. Pick INSTANT/INPLACE, or use gh-ost / pt-osc, before you run it at peak.
7. REPEATABLE READ + next-key (gap) locks → short, consistently-ordered transactions. This is the InnoDB default and the usual deadlock source. Acquire rows in the same order everywhere.
8. Measure with EXPLAIN ANALYZE, do not guess. The optimizer's rows is an estimate; EXPLAIN ANALYZE runs the query and reports actual rows and timing.

Index decision

-- Bad: separate single-column indexes; the optimizer uses at most one, then filesorts.
CREATE INDEX idx_uid ON orders (user_id);
CREATE INDEX idx_created ON orders (created_at);
-- Query: WHERE user_id = ? AND created_at >= ? ORDER BY created_at DESC

-- Good: one composite index — equality (user_id) first, then the range/sort column.
-- This serves the WHERE and the ORDER BY with no separate sort step.
CREATE INDEX idx_user_created ON orders (user_id, created_at);

Read EXPLAIN

EXPLAIN shows the plan; EXPLAIN ANALYZE runs it and reports actual rows/time; EXPLAIN FORMAT=JSON shows cost and used-key-parts. Read the access type first — it is the ladder from worst to best:

ALL (full scan) → index (full index scan) → range → ref → eq_ref → const.

Anything ALL on a large table is a red flag. Then check rows (estimated rows examined), filtered (% surviving the WHERE), and the Extra flags: Using filesort (extra sort pass), Using temporary (materialised temp table), Using index (covering — good, no back-lookup).

The most common cause of a missed index is a non-sargable predicate — a function or implicit charset/type cast wrapping the indexed column:

-- Bad: DATE() wraps the indexed column → the index on created_at can't be used → type=ALL.
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-01';

-- Good: range over the raw column → index range scan (type=range).
SELECT * FROM orders
WHERE created_at >= '2026-06-01' AND created_at < '2026-06-02';

A subtler version: joining a utf8mb4 column to a latin1 column, or a VARCHAR to an INT, forces a per-row cast and disables the index. Make both sides the same type and collation. Full field-by-field reading, the type ladder, and every "why no index" cause are in references/indexing-and-explain.md.

Online DDL chooser

-- INSTANT: adding a column at the end is metadata-only in 8.0+. Always be explicit so a
-- silent fall-through to COPY (which locks) can't happen.
ALTER TABLE orders ADD COLUMN note VARCHAR(255) NULL, ALGORITHM=INSTANT, LOCK=NONE;

# gh-ost: build a shadow table, copy + tail the binlog, then atomic cutover. Always --dry-run
# first; throttle on replica lag so you don't melt production.
gh-ost \
  --host=primary.db --database=shop --table=orders \
  --alter="ADD INDEX idx_user_created (user_id, created_at)" \
  --max-lag-millis=1500 --throttle-control-replicas="replica1.db" \
  --execute   # drop --execute to dry-run

If gh-ost refuses to read the binlog, run pt-online-schema-change, which uses triggers instead. Both, plus the rollback path and how this composes with db-migrations expand-contract theory, are in references/online-ddl-and-migrations.md.

Copy-paste patterns

-- Covering index: the query reads only (user_id, status, total), so put them all in the index.
-- EXPLAIN then shows "Using index" — no trip back to the PK leaf for each row.
SELECT status, total FROM orders WHERE user_id = ?;
CREATE INDEX idx_cover ON orders (user_id, status, total);

-- GTID replication on the replica: GTID auto-positioning, no log file/pos bookkeeping.
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary.db', SOURCE_USER='repl', SOURCE_PASSWORD='***',
  SOURCE_SSL=1, SOURCE_AUTO_POSITION=1;
START REPLICA;

-- Replica lag: read the field, don't eyeball. Seconds_Behind_Source is coarse; for accuracy use
-- performance_schema replication tables. NULL means replication is broken, not "0 lag".
SHOW REPLICA STATUS\G   -- Replica_IO_Running / Replica_SQL_Running / Seconds_Behind_Source
SELECT * FROM performance_schema.replication_applier_status_by_worker;

-- Deadlock post-mortem: InnoDB rolls back the cheaper transaction and logs the cycle here.
SHOW ENGINE INNODB STATUS\G   -- read the LATEST DETECTED DEADLOCK section

# Consistent logical dump without locking every table: single transaction over InnoDB.
mysqldump --single-transaction --set-gtid-purged=AUTO --routines --triggers shop > shop.sql

Replication topologies (async / semi-sync / group replication / InnoDB Cluster + MySQL Router), failover, and read-replica routing are in references/replication-and-ha.md.

MySQL vs MariaDB divergence

They share a heritage and diverge in ways that break copy-pasted SQL. Do not assume parity.

Depth and both-direction migration gotchas: references/mysql-vs-mariadb.md.

Anti-patterns / rationalizations → STOP

Verify

Run scripts/verify.sh from your project root. It is read-only and never connects to a database — it heuristically lints discovered *.sql and *.cnf/my.cnf files for the foot-guns above (legacy utf8, MyISAM, random-UUID PK, binlog_format=STATEMENT, mysql_native_password, function-wrapped indexed columns) and checks balanced delimiters. It exits non-zero only on unbalanced delimiters or a committed binlog_format=STATEMENT; every schema heuristic is advisory. It optionally runs sqlfluff --dialect mysql if installed.

See Also

• ../sql/SKILL.md — portable, engine-agnostic SELECT/JOIN/window-function craft.
• ../postgresdb/SKILL.md — the peer engine (PostgreSQL): MVCC, VACUUM, RLS, JSONB.
• ../planetscale/SKILL.md — the PlanetScale/Vitess platform workflow on top of MySQL.
• db-migrations — vendor-neutral migration strategy (expand-contract) that the ALGORITHM=/gh-ost mechanics here ride on.
• backups — backup strategy, retention, and restore drills as a discipline.