Unique Key Lookup — single-row index lookupMySQL 8.4 • MariaDB 11.4

Descend a UNIQUE B+tree to exactly one leaf entry; if the index is non-covering, take one PK-hop to the clustered row.

Lesson familyIndex Access

Parameters (single-row unique lookup)

Logarithmic: 1K, 10K, 100K, 1M, 10M, 100M, 1B
Covering removes the final table-row read — watch the PK-hop arrow disappear.

Unique key lookup animation

Example query this animation executes

-- Single-row lookup on a UNIQUE key
SELECT id, email
FROM   users
WHERE  id = 42;   -- uq_users_id guarantees at most 1 row

In EXPLAIN this appears as Single-row index lookup (eq_ref / const): descend the B+tree to exactly one leaf entry; non-covering plans then do one clustered-row fetch.

What you'll see in the animation

  • The grey rectangles are B+tree pages of the UNIQUE index (uq_users_id). Top row is the root page, bottom row is the leaf.
  • A red diamond 'query token' appears above the root and descends level by level — each step is one page read.
  • When the token arrives at a page, the page pulses yellow. That's 'page is in the buffer pool now'.
  • Because the index is UNIQUE, the leaf stores at most ONE matching entry for id=42 — a small chip next to the leaf says so. No range scan, no sibling-page walk.
  • Covering mode: the leaf already has every column the query asked for, an 'Index-only' badge glows on the leaf, and the lookup stops there.
  • Non-covering mode: the leaf stored the PK. A dashed orange PK-hop arrow appears to the clustered tree and the token rides that arrow across to fetch the full row.
Adjust parameters, then press Play
0.0s 0.0s

Cost readout (single-row unique lookup)

Tree height ?Number of B+tree levels from root to leaf. InnoDB's high fan-out keeps this tiny: a billion-row table is only 4 levels deep.

—

Index reads ?Pages touched while traversing the UNIQUE index to the matching entry. Equals tree height.

—

Row fetches ?Clustered table-row fetches after the index hit. 0 if covering, 1 if non-covering.

—

Total work (pages) ?Index reads + row fetches. O(log n) covering, O(log n + 1) non-covering.

—

Unique lookup vs full scan (log–log, InnoDB fan-out ≈ 800)

Learn more — unique vs non-unique lookup

Unique lookup returns at most one row for a key value. MySQL's optimizer labels this access path const or eq_ref, and EXPLAIN ANALYZE prints Single-row index lookup. Because the upper bound is 1, the planner can skip a bunch of bookkeeping that range scans need — there's no sibling-leaf walk, no "stop when the next key changes" check, no MRR.

Non-unique lookup (see the sibling lesson) can match many rows for one value. That means a range of leaf entries on the secondary tree, and potentially many clustered-row fetches when non-covering. The I/O scales with the number of matches — not just with log n.

Covering unique index — when every column in the SELECT list is stored in the unique index's leaf, the lookup never touches the clustered B+tree. This is the fastest shape of point read that InnoDB can do: a single descent of O(log n) pages.

Sources: MySQL 8.4 Reference Manual §8.2.1.1 (WHERE clause optimization), §17.6.2 (InnoDB Indexes).