Non-Unique Key Lookup — index hits that return many rowsMySQL 8.4 • MariaDB 11.4

Understand how B+tree traversal, leaf-range scanning, and row-id fetches compose the real work of this operator.

Lesson familyIndex Access

Parameters (non-unique lookup)

Table rows: 1000000
Percent of table rows whose leaf entries match country='US': 2.0%
Covering avoids the second table lookup per match.

Example query this animation executes

SELECT id, name, country
FROM users
WHERE country = 'US';

With index idx_users_country(country), this appears in EXPLAIN as Index lookup / Index range scan: descend the secondary B+tree, scan matching leaf entries, then follow row-id pointers.

What you'll see in the animation

  • The orange token is your predicate value (country='US') moving through root, internal, and leaf B+tree pages.
  • At the leaf level, one key value maps to many entries: each entry stores a row-id pointer.
  • Non-covering path: every matched entry triggers a clustered-row fetch by row-id (extra I/O).
  • Covering path: if selected columns are in the index payload, row-id fetches are skipped.
Ready — press Play
0.0s 0.0s

Cost readout (B+tree traversal + leaf scan + row-id fetch)

B+tree height ?Approximate number of index pages traversed to reach matching leaf range.

—

Descent page reads ?Root + internal + first matching leaf page reads before scanning the range.

—

Matched leaf entries ?Index entries in the key/range condition. Non-unique means this can be many.

—

Index reads ?Tree traversal + matched index entries touched.

—

Clustered row-id fetches ?Extra table-row reads for non-covering lookups (one per matched entry).

—

Total reads (rough) ?Index reads + clustered row fetches. Covering index removes row-fetch part.

—

Work vs table size (log–log, selectivity fixed)

Learn more — what actually happens in the background

For country='US', MySQL first descends the secondary B+tree (root -> internal -> first matching leaf). Then it walks adjacent leaf entries while the key still matches US.

Each matching leaf entry contains the primary key (row-id) pointer. On non-covering plans, that pointer triggers another lookup in the clustered PRIMARY tree to fetch full row columns.

That second hop is the expensive part. If the index is covering, the operator can return directly from leaf payload and skip clustered fetches.