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 ask	Goes to
Portable query craft — joins, window functions, CTEs, NULL 3VL	sql
PostgreSQL engine behaviour — MVCC, VACUUM, RLS, JSONB, PgBouncer	postgresdb
PlanetScale / Vitess branch + deploy-request workflow, no-FK design	planetscale
Vendor-neutral migration theory — expand-contract, batched backfill	db-migrations
ORM / query-builder API ergonomics	drizzle-orm, prisma-orm
Backup strategy / retention / restore drills as a discipline	backups
OLAP / columnar analytics	clickhouse-analytics, duckdb

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.

Target	Use it when	Watch out
MySQL 8.4 LTS	Default for conservative production. GA 2024-04-30, supported through April 2032.	mysql_native_password is disabled by default here.
MySQL 9.x Innovation	Only if you need VECTOR or the newest features and accept short support.	Short-lived track; mysql_native_password is removed. Not for stable prod.
MariaDB 11.8 LTS	The fork; 2025 yearly LTS, first MariaDB LTS with native vector search.	Auth, vector syntax, and RETURNING differ from MySQL — not drop-in compatible; innodb_snapshot_isolation defaults ON.

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

You have	Use
One column in WHERE, high selectivity	Single-column index
Multiple WHERE columns + an ORDER BY	Composite index: equality cols first, then range/sort col (leftmost prefix)
Query reads only indexed columns	Covering index (add the selected cols) — avoids the PK back-lookup
Filtering a long TEXT/VARCHAR prefix	Prefix index col(20) — can't be covering, watch selectivity
Rolling out an index on a hot table safely	INVISIBLE index, then flip VISIBLE once verified

-- 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

Operation / situation	Use
Add column at end, rename column, set default, drop index	ALGORITHM=INSTANT — metadata-only, near-free (8.0+)
Add secondary index, change column nullability inplace	ALGORITHM=INPLACE, LOCK=NONE — rebuilds without blocking most writes
What INSTANT/INPLACE can't do, on a small/cold table	ALGORITHM=COPY — locks + rebuilds; fine off-hours
Same change on a large/hot table, zero downtime	gh-ost or pt-online-schema-change — shadow table + swap

-- 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.

Area	MySQL 8.4 / 9.x	MariaDB 11.8
Default auth	caching_sha2_password	mysql_native_password / ed25519
VECTOR	MySQL 9.0+ only; STRING_TO_VECTOR()	Native in 11.8; VEC_DISTANCE_COSINE() — different syntax
RETURNING	INSERT ... RETURNING only (8.0+)	INSERT/UPDATE/DELETE ... RETURNING
Sequences	No CREATE SEQUENCE	CREATE SEQUENCE supported
System-versioned (temporal) tables	Not supported	WITH SYSTEM VERSIONING supported
Snapshot isolation	RR snapshot, no write-conflict detection	innodb_snapshot_isolation defaults ON
JSON	Native binary JSON type	Historically a LONGTEXT alias; check version

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

Anti-patterns / rationalizations → STOP

Rationalization	Reality	Do instead
"utf8 is Unicode, it's fine."	utf8 = 3-byte utf8mb3; emoji silently become ????.	utf8mb4 at the column level.
"A random UUID PK is clean and unique."	Random PK bloats every secondary index and kills insert locality in the clustered index.	BIGINT AUTO_INCREMENT or ordered UUIDv7 as BINARY(16).
"STATEMENT binlog is smaller, use it."	Non-deterministic statements replicate wrong; silent data drift on replicas.	binlog_format=ROW.
"Just keep using mysql_native_password."	Disabled by default in 8.4, removed in 9.0 — your upgrade breaks.	caching_sha2_password + TLS.
"Wrapping the column in DATE()/LOWER() is readable."	Function on an indexed column → full scan.	Rewrite to a sargable range; or add a generated column + index.
"SELECT * is convenient."	Pulls wide InnoDB rows off-disk and defeats covering indexes.	Select only needed columns.
"I'll hold the transaction open while I do other work."	RR + gap locks held long → deadlocks and lock waits everywhere.	Keep transactions short; commit fast; order rows consistently.
"ALTER it now, traffic is fine."	COPY algorithm locks a multi-GB table; outage at peak.	Pick INSTANT/INPLACE, or gh-ost off-peak.
"EXPLAIN says 12 rows, so it's fast."	rows is an estimate from stats.	Confirm with EXPLAIN ANALYZE (actual rows/time).
"MyISAM is faster for our table."	No transactions, no FKs, table-level locks, crash-unsafe.	InnoDB for anything transactional.

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.