MySQL

First-principles depth — the clustered index, query planning, transactions, locking, durability & replication.

GlossaryContext: InnoDB, MySQL 8.0+

Lessons

0001 The clustered index
The table is a B+tree by primary key — so a secondary read is a double lookup, and PK choice is a storage decision.
done
0002 Composite indexes & the leftmost-prefix rule
A composite index is a phone book — only leftmost prefixes are searchable, so column order is a design decision.
done
0003 Reading EXPLAIN
Grade any plan in one pass: access type, key, rows × filtered, Extra — plus EXPLAIN ANALYZE for estimates vs reality.
done
0004 When indexes are ignored
The four killers — wrapped columns, leading wildcards, type mismatches, low selectivity — and the fix for each.
done
0005 Transactions & isolation levels
A plain read is a lock-free MVCC snapshot; the isolation level just times it. RR vs RC, and the three anomalies.
done
0006 InnoDB locking & deadlocks
Locks sit on index records; record/gap/next-key locks; the deadlock cycle, the victim, and how to retry.
done
0007 Durability: redo & undo logs
Write-ahead logging: redo rolls forward on crash, undo rolls back (and powers MVCC); the flush-at-commit dial.
done
0008 Replication & the binary log
The binlog as a logical change feed; receiver/applier threads; async vs semisync and the failover data-loss window.
done
0009 Interleaved review
All eight topics mixed on purpose — a cross-pillar quiz plus diagnose-it scenarios to convert fluency into durable memory.
done

These lessons have a teacher attached. Bring a real EXPLAIN plan, a slow query, or a fuzzy concept to your session and we'll dissect it together.