SQL Index Deep Dive > How MySQL Really Executes Your Queries

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 → only household_id used for seek; deleted_at IS NULL handled by ICP
key_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 row
Y = 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.