Start here: recommended order
- 01Full table scan: why every row gets read
- 02B+tree lookup: how InnoDB finds a row
- 03Unique key lookup: single-row index lookup
- 04Non-unique key lookup: index hits that return many rows
- 05Index condition pushdown: filtering inside InnoDB
- 06Covering index: why "Using index" in EXPLAIN is the goal
- 07Nested loop join: dedicated operator view
- 08Hash join: build, probe, and grace-hash spill
- 09Block nested loop join: MariaDB's default for non-indexed joins
- 10Batched key access (BKA) join: batch, sort, MRR
- 11Semijoin FirstMatch: stop looking after the first match
- 12Semijoin duplicate weedout: dedup via temp table
- 13Derived table materialization: a subquery becomes a temp table
- 14Join-order search: why query planning is factorial
- 15InnoDB buffer pool: midpoint-insertion LRU
- 16InnoDB buffer pool: cold-start vs warm, dump/load cure
Scan, sort and temp family6
- Filesort: how MySQL sorts without an indexsort_buffer_size, sorted runs, k-way merge. Why ORDER BY is slow without an index.
- Temporary tables: in-memory to on-disk conversionWatch GROUP BY hit the in-memory temp table limit and convert to on-disk InnoDB (Aria on MariaDB).
- Full table scan: why every row gets readSee O(n) row reads in action and compare against indexed access O(log n + k).
- Filter operator: WHERE predicate row-by-rowRows entering filter are all evaluated; only matching rows continue.
- Derived table materialization: a subquery becomes a temp tableFROM-clause subquery materialized into temp table, auto-indexed, then probed.
- Covering index: why "Using index" in EXPLAIN is the goalNon-covering vs covering plan; the InnoDB PK-append trick that silently covers many queries.
Index access family7
- B+tree lookup: how InnoDB finds a rowClustered primary key vs secondary-to-clustered; 16 KiB page fan-out.
- Non-unique key lookup: index hits that return many rowsUnderstand Index lookup / Index range scan and non-covering row fetch cost.
- Unique key lookup: single-row index lookupExact-key lookup path and why covering indexes can skip table-row fetch.
- Index condition pushdown: filtering inside InnoDBSee how ICP checks trailing index columns before fetching the row.
- Index merge: combining two index scansUnion, intersection, sort-union: two indexes are better than a full table scan.
- Skip scan: range access without the leading index columnLow-NDV leading column lets MySQL do N small range scans instead of a full table scan.
- Rowid filter: a sorted key pre-filter before table accessMariaDB scans a filtering index into a sorted array of primary keys, skipping table reads for entries not in it.
Join family7
- Batched key access (BKA) join: batch, sort, MRRBatch outer keys, sort by rowid, and sweep the inner index sequentially via Multi-Range Read.
- Block nested loop join: MariaDB's default for non-indexed joinsWatch join_buffer_size decide how many times the inner table is rescanned.
- Hash join: build, probe, and grace-hash spillMySQL 8.4's default for non-indexed equi-joins; animated build + probe phases.
- BNL vs hash join: side by sideMariaDB BNL (default) vs MySQL 8.4 hash join; move the sliders and feel the asymptotic difference.
- Nested loop join: dedicated operator viewSingle-operator view of the outer-driver/inner-probe loop shape from EXPLAIN.
- Semijoin FirstMatch: stop looking after the first matchIN/EXISTS as a semijoin; the inner scan short-circuits the moment one matching row is found.
- Semijoin duplicate weedout: dedup via temp tableIN/EXISTS rewritten as inner join; a temp table keyed on outer rowid removes duplicates.
Cache and memory family2
Planner and optimizer family1
No lessons match.