Unique key lookup: single-row index lookupMySQL 8.4 • MariaDB 11.4

Descend a UNIQUE B+tree to exactly one leaf record; if the index is non-covering, walk the clustered index for the row.

Lesson familyIndex access

Parameters (single-row unique lookup)

About this example

Unique key lookup animation

Ready: press Next step, or Play to run it all
0.0s 0.0s

About this example

Example query this animation executes

-- Single-row lookup on a UNIQUE secondary index
SELECT id, email, first_name
FROM   users
WHERE  email = 'alice@example.com';   -- uq_users_email: UNIQUE (email)

EXPLAIN shows type const here (eq_ref when the email comes from another table in a join). MySQL reads a const table once while it makes the plan, so EXPLAIN ANALYZE shows Rows fetched before execution; an eq_ref lookup prints as Single-row index lookup. Switch Covering to drop first_name and the clustered index walk.

What you'll see in the animation

  • The grey rectangles are B+tree pages of the UNIQUE index uq_users_email. The top row is the root page, the middle rows hold internal pages and the bottom row holds leaf pages.
  • A red diamond 'query token' appears above the root page and goes down one page at a time. Each step is one page read; Next step shows one read at a time.
  • 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, at most ONE leaf record can hold alice@example.com. A small chip under the leaf says so: no range scan, no next-record check.
  • Covering mode: the leaf record (email, id) already has every column SELECT id, email asks for, an 'Index-only' badge appears, and the lookup stops there.
  • Non-covering mode: first_name is not in the record, which stores the primary key (PK = 42). A dashed orange arrow links it to the clustered leaf that holds the row: the key, not a disk address, is the link, so InnoDB then walks the clustered tree from its root page.
  • The simplified code beside the drawing highlights the lines each step runs.

Cost readout (single-row unique lookup)

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

—

Unique index reads ?Pages read in uq_users_email to reach the one matching record: one per level, from the root page to the leaf page.

—

Clustered index reads ?Pages read in the clustered index to get first_name. The unique record holds the primary key, so InnoDB walks the clustered index from its root page: one page per level. 0 when the index covers the query.

—

Pages touched ?Unique index reads plus clustered index reads for this one-row lookup: O(log n) covering, O(2 log n) non-covering.

—

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

Learn more — unique vs non-unique lookup

Unique lookup returns at most one row for a key value. The optimizer labels this access path const when the value is a constant, as here, or eq_ref when it comes from another table in a join. MySQL and MariaDB read a const table once while they make the plan, so MySQL's EXPLAIN ANALYZE shows it as Rows fetched before execution; an eq_ref lookup prints as Single-row index lookup. Because the upper bound is 1, there's no sibling-leaf walk and no "stop when the next key changes" check. A UNIQUE index can still hold many NULLs, so WHERE email IS NULL is not a single-row lookup.

Non-covering unique lookup: the leaf record of uq_users_email holds (email, id). To return first_name, InnoDB takes the primary key from the record and walks the clustered index from its root page to the leaf that holds the row, the same second walk as in the B+tree lesson.

Non-unique lookup (see the sibling lesson) can match many rows for one value. That means a range of leaf records on the secondary tree, and one clustered index lookup per match 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 record (InnoDB keeps the primary key there too), the lookup never touches the clustered B+tree. It costs the same as a primary key lookup: 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).