Parameters (single-row unique lookup)
About this exampleUnique key lookup animation
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).