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.