A practical walkthrough of MySQL index internals, execution plans, and query optimization — using a real-world financial schema.
The Schema
CREATE INDEX idx_assets_hh_kind_del ON assets (household_id, kind, deleted_at);
This composite index is the subject of every example below.
MySQL Architecture: Two Layers
Before diving into indexes, understanding the two-layer MySQL architecture is essential.
Client (app / DB client) │ ▼ ┌─────────────────────────────────────────┐ │ MySQL Server Layer │ │ 1. Parser → SQL string to AST │ │ 2. Optimizer → choose index, plan │ │ 3. Executor → run plan, call engine │ └─────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────┐ │ Storage Engine (InnoDB) │ │ - B-Tree index management │ │ - Buffer pool (page cache) │ │ - ICP filtering at index level │ │ - Disk I/O when page not cached │ └─────────────────────────────────────────┘
The server layer handles SQL parsing, optimization, and post-fetch filtering. The storage engine handles raw data access. This separation is why some filters happen "later than expected" — the storage engine can only act on what it can see in the index.
How B-Tree Indexes Store Data
A B-Tree index stores leaf nodes sorted by the index columns in order. For (household_id, kind, deleted_at):
[hh=1, CASH, NULL] → ptr → row [hh=1, CASH, NULL] → ptr → row [hh=1, CRYPTO, NULL] → ptr → row [hh=1, GOLD, NULL] → ptr → row [hh=1, GOLD, NULL] → ptr → row [hh=1, REAL_ESTATE, NULL] → ptr → row [hh=2, CASH, NULL] → ptr → row ← boundary
Rows with the same household_id are contiguous. Within the same household, rows are sorted by kind. This ordering is what makes GROUP BY free in certain cases.
EXPLAIN Output: Key Fields
EXPLAIN SELECT kind, SUM(purchase_price) AS total FROM assets WHERE household_id = 1 AND deleted_at IS NULL GROUP BY kind ORDER BY total DESC;
With small data (61 rows)
type: index ← full index scan (not a seek) key: idx_assets_hh_kind_del key_len: 17 ← all 3 columns used rows: 61 Extra: Using where; Using temporary; Using filesort
With large data (100k rows)
type: ref ← index seek on household_id ✓ key: idx_assets_hh_kind_del key_len: 8 ← only household_id used for seek ref: const rows: 49,589 Extra: Using index condition; Using temporary; Using filesort
Key takeaway: with 61 rows, the optimizer chose a full index scan because seeking vs scanning was nearly the same cost. At 100k rows, it switched to a proper index seek. The optimizer self-tunes based on data volume.
Understanding key_len
key_len shows how many bytes of the index are used for the seek/ref operation — not for ICP.
| Column | Type | Bytes |
|---|---|---|
household_id | BIGINT UNSIGNED NOT NULL | 8 |
kind | ENUM (6 values) NOT NULL | 1 |
deleted_at | DATETIME(3) NULLABLE | 8 |
key_len = 8→ onlyhousehold_idused for seek;deleted_at IS NULLhandled by ICPkey_len = 17→ all 3 columns used (full index scan mode)
Three EXPLAIN Extra Values Explained
Using index (best)
All data needed is in the index itself — no data page reads.
-- kind and household_id are both in the index SELECT kind FROM assets WHERE household_id = 1;
Index entry: [hh=1, GOLD, NULL] → kind is here, done No clustered index lookup needed EXPLAIN Extra: Using index
Using index condition (ICP)
A filter is pushed down to the storage engine and evaluated at the index level, before reading the row.
WHERE household_id = 1 AND deleted_at IS NULL -- deleted_at is in the index → ICP applies
Storage engine checks deleted_at IS NULL at the index entry. If it fails → skip, do NOT follow pointer to data page. Only rows that pass ICP trigger a data page read.
Using where
A filter exists that the storage engine cannot evaluate — the row is read first, then the server layer applies the filter.
WHERE household_id = 1 AND currency = 'USD' -- currency is NOT in the index
Storage engine: seeks hh=1, reads rows up to server layer Server layer: filters currency = 'USD' on received rows Rows that fail the filter were read for nothing
Clustered Index Lookups
In InnoDB, the secondary index only stores index columns + primary key. If the query needs a column not in the index:
Secondary index entry: [hh=1, GOLD, NULL, id=42] ↓ Follow ptr → clustered index (id=42) ↓ Read full row → get purchase_price
Every row that passes ICP triggers one random I/O into the clustered index. This is the main bottleneck at scale.
Fix: Covering Index
Include the queried column in the index:
INDEX (household_id, kind, deleted_at, purchase_price)
Now purchase_price is available directly in the index entry. No data page lookup needed. EXPLAIN shows Using index.
GROUP BY: With vs Without Temp Table
Case 1 — GROUP BY column is in the index (no temp table)
-- Index: (household_id, kind, deleted_at) SELECT kind, COUNT(*) FROM assets WHERE household_id = 1 GROUP BY kind;
After seeking to household_id = 1, rows are already sorted by kind in the index:
CASH, row CASH, row GOLD, row ← kind changes → close CASH group, emit COUNT GOLD, row STOCK, row ← kind changes → close GOLD group, emit COUNT
MySQL streams through in one pass, keeping only one accumulator in memory at a time. No temp table needed.
Case 2 — GROUP BY column is not in the index (needs temp table)
-- currency is not in the index SELECT currency, COUNT(*) FROM assets WHERE household_id = 1 GROUP BY currency;
Rows arrive ordered by kind (from the index), so currency values are interleaved:
VND, USD, VND, VND, USD, VND ...
MySQL cannot close a group early because VND might appear again later. It must:
- Read all rows into a temp table
- Filesort the temp table by
currency - Stream through the sorted result to GROUP BY
EXPLAIN Extra: Using temporary; Using filesort
The cost difference
| Case 1 | Case 2 | |
|---|---|---|
| Rows into temp table | 0 | N (all rows) |
| Filesort input size | ~6 rows (aggregates) | N rows (raw data) |
| Memory peak | 1 accumulator | Full result set |
| Risk | None | Temp table spills to disk if > tmp_table_size |
When the temp table exceeds tmp_table_size, MySQL writes to disk — this is when query latency spikes dramatically.
EXPLAIN ANALYZE: Real Execution Numbers
EXPLAIN ANALYZE SELECT kind, SUM(purchase_price) AS total FROM assets WHERE household_id = 1 AND deleted_at IS NULL GROUP BY kind ORDER BY total DESC;
-> Sort: total DESC actual time=91.4..91.4 rows=6 -> Stream results actual time=29.1..91.4 rows=6 -> Group aggregate: sum(purchase_price) actual time=29.1..91.4 rows=6 -> Index lookup using idx_assets_hh_kind_del (household_id=1) with index condition: (deleted_at is null) estimated rows=49,589 actual rows=100,059 actual time=0.631..84.9
The actual time=X..Y format:
X= time to return the first rowY= time to return the last row
Time breakdown
0ms 0.6ms 84.9ms 91.4ms ├──────────┼──────────────────────────────┼───────┤ │ Index lookup + row reads │ Sort │ │ ~84ms │ ~0ms │ ↑ bottleneck
92% of total time is reading 100k rows from data pages to get purchase_price.
Estimation vs reality
| Estimated | Actual | |
|---|---|---|
| Rows from index lookup | 49,589 | 100,059 |
deleted_at IS NULL selectivity | 10% | ~100% |
The optimizer was off by 2x. Running ANALYZE TABLE assets updates statistics and improves estimates.
Selectivity Analysis
Selectivity = distinct values / total rows Range: 0 to 1 — closer to 1 means the filter eliminates more rows
| Column | Distinct Values | Selectivity | Effectiveness |
|---|---|---|---|
household_id | ~10 | ~0.0001 | Good for seek — isolates 1/10 of table |
deleted_at IS NULL | 2 | ~0.00002 | Nearly useless — almost all rows are NULL |
kind | 6 | ~0.00006 | Low — ~16k rows per group |
The deleted_at problem
deleted_at IS NULL has extremely low selectivity because data is skewed — nearly 100% of rows have deleted_at = NULL (nothing is deleted). The filter survives in the index because it matters for correctness, but it contributes almost nothing to reducing I/O.
Impact of household distribution
If all 100k rows belong to household_id = 1, the seek reduces nothing. With realistic distribution (10 households, 10k rows each), the same query touches only 10k rows instead of 100k — ~8x faster.
Full Execution Flow: Current Query
Index seek: household_id = 1 → ICP: check deleted_at IS NULL at index level → fail → skip row (no data page read) → pass → follow ptr → clustered index → read purchase_price → Feed into SUM accumulator for current kind group → When kind changes → emit group, open new accumulator → After all rows → 6 aggregate rows → Filesort 6 rows by total DESC → Return
Optimization Path
| Change | Effect |
|---|---|
Add purchase_price to index | Eliminates 100k clustered index lookups → Using index |
| Multiple households in data | household_id seek becomes effective |
ANALYZE TABLE | Fixes optimizer estimate (49k → 100k) |
Remove ORDER BY if not needed | Eliminates the final sort buffer |
Covering index (most impactful)
ALTER TABLE assets ADD INDEX idx_assets_hh_kind_del_val (household_id, kind, deleted_at, purchase_price);
With this index, the entire query resolves from the index tree — no data page reads, no clustered index lookups. Expected time: from ~84ms to ~5ms for 100k rows.