Reference · Cheat sheet

The InnoDB clustered index

The one-page compressed essence: an InnoDB table is a B+tree ordered by its primary key.

Lesson: 0001Context: InnoDB, MySQL 8.0+

The one idea

Keystone

The table is the primary-key B+tree. Leaf nodes of the clustered index hold the entire row, in PK order. There is no separate "heap" of rows — the index is the storage.

Clustered vs secondary — the difference that matters

	Clustered index	Secondary index
How many	Exactly one per table	Any number
Ordered by	Primary key	The indexed column(s)
Leaf holds	The full row	Indexed cols + PK columns
"Pointer" to row	Is the row	The PK value (not a disk address)
Reads to get a full row	One B+tree descent	Two (unless covering)

What a secondary lookup really does

secondary index (e.g. on `email`) clustered index (by `id` = PK) ┌───────────────────────────┐ ┌────────────────────────────┐ │ email → (id) │ step 2 │ id → { id, name, email, …} │ │ a@x.com → (42) ──────────┼─────────────▶│ 42 → { full row } │ └───────────────────────────┘ look up PK └────────────────────────────┘ step 1: find PK by email fetch the rest of the row

Two B+tree descents for one row. A covering index that already holds every column the query needs skips step 2 entirely (Using index in EXPLAIN).

Rules that fall out of it

Keep the PK short. Every secondary index copies the full PK into every entry. A fat PK (e.g. a long string) bloats all indexes. — MySQL: "advantageous to have a short primary key"

Prefer a monotonic PK. An AUTO_INCREMENT integer appends to the "right edge" of the tree — cheap. A random PK (random UUID) inserts everywhere, causing page splits and fragmentation.

Always define an explicit PK. Without one InnoDB builds a hidden GEN_CLUST_INDEX on a 6-byte row ID you can't use — you pay for a clustered index and get nothing queryable.

PK-ordered range scans are sequential. WHERE id BETWEEN … walks contiguous leaf pages; a range on a random column scatters across the tree.

Design covering indexes for hot queries. Put every column a frequent query reads into one secondary index so it never touches the clustered index.

No-PK fallback (in order)

Priority	What InnoDB clusters on
1	The declared `PRIMARY KEY`
2	The first `UNIQUE` index with all columns `NOT NULL`
3	A hidden `GEN_CLUST_INDEX` on a synthetic 6-byte row ID

See it yourself

-- Answered from the index alone → "Using index" (covering)
EXPLAIN SELECT id FROM users WHERE email = 'a@x.com';

-- Needs other columns → secondary lookup + clustered fetch
EXPLAIN SELECT name FROM users WHERE email = 'a@x.com';

Source: MySQL 8.0 Manual — Clustered and Secondary Indexes · All lessons