myflames · Learn

Interactive MySQL 8.4 & MariaDB 10.11+ execution lessons

Start here: recommended order

  1. 01Full table scan: why every row gets read
  2. 02B+tree lookup: how InnoDB finds a row
  3. 03Unique key lookup: single-row index lookup
  4. 04Non-unique key lookup: index hits that return many rows
  5. 05Index condition pushdown: filtering inside InnoDB
  6. 06Covering index: why "Using index" in EXPLAIN is the goal
  7. 07Nested loop join: dedicated operator view
  8. 08Hash join: build, probe, and grace-hash spill
  9. 09Block nested loop join: MariaDB's default for non-indexed joins
  10. 10Batched key access (BKA) join: batch, sort, MRR
  11. 11Semijoin FirstMatch: stop looking after the first match
  12. 12Semijoin duplicate weedout: dedup via temp table
  13. 13Derived table materialization: a subquery becomes a temp table
  14. 14Join-order search: why query planning is factorial
  15. 15InnoDB buffer pool: midpoint-insertion LRU
  16. 16InnoDB buffer pool: cold-start vs warm, dump/load cure

Scan, sort and temp family6

Index access family7

Join family7

Cache and memory family2

Planner and optimizer family1