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 Duplicate Weedout — dedup via temp table
- 12Derived Table Materialization
- 13Join-order search — why query planning is factorial
- 14InnoDB buffer pool — midpoint-insertion LRU
- 15InnoDB buffer pool — cold-start vs warm, dump/load cure
Scan / Sort / Temp Family
- Filesort — how MySQL sorts without an indexsort_buffer_size, sorted runs, k-way merge. Why ORDER BY is slow without an index.
- Temporary tables — MEMORY to on-disk conversionWatch GROUP BY hit the tmp_table_size limit and convert to on-disk InnoDB.
- 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 MaterializationFROM-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 Family
- 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 — bitmap pre-filter before table accessMariaDB scans a filtering index to build a rowid bitmap, skipping table fetches for non-matching rows.
Join Family
- 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 Duplicate Weedout — dedup via temp tableIN/EXISTS rewritten as inner join; a temp table keyed on outer rowid removes duplicates.