Parameters (non-unique lookup)
About this exampleAbout this example
Example query this animation executes
SELECT id, name, country FROM users WHERE country = 'US';
With index idx_users_country(country), EXPLAIN ANALYZE shows Index lookup on users using idx_users_country (Covering index lookup when the index holds every column; a range condition shows as Index range scan): descend the secondary B+tree, read the matching leaf entries, and look each entry's primary key up in the clustered index.
What you'll see in the animation
- The pill is your predicate value (country='US') moving down the root page, an internal page and the leaf pages of idx_users_country. The page it reaches turns yellow.
- At the leaf pages, one key value maps to many entries, sorted by (country, id). Each entry stores the row's primary key (id), not a disk address. The drawing shows 6 of them.
- Non-covering path: name is not in the entry, so each entry's id is looked up in the clustered index (the dot travels right) before the next entry is read.
- Covering path: SELECT id, country needs nothing else, so the rows are sent straight from the entries.
- The run ends at the first entry with another country (UY). Next step shows one page or one entry at a time, and the simplified code beside the drawing highlights the lines each step runs.
Cost readout (B+tree descent + leaf entries + clustered index lookups)
B+tree height ?Approximate number of index pages from the root page to the leaf page where the 'US' entries start.
—
Descent page reads ?Root page + internal pages + the first leaf page with a 'US' entry, read before the first match.
—
Matched leaf entries ?Index entries with country = 'US'. Non-unique means this can be many: rows times the selectivity.
—
Index reads ?Descent pages plus matching entries read: a rough count of index work.
—
Clustered index lookups ?For a non-covering lookup, one clustered index lookup per matching entry, each a walk from the root page to the leaf page with that id. 0 when the index covers the query.
—
Total reads (rough) ?Index reads plus clustered index lookups. A covering index removes the lookups.
—
Work vs table size (log–log, selectivity fixed)
Learn more — what actually happens in the background
For country='US', InnoDB first descends the secondary B+tree (root page, internal pages, first
leaf page with a 'US' entry). Then it reads the following entries, crossing to the next leaf page when one ends,
while the key still matches US. The first entry with another country ends the run.
Each leaf entry holds (country, id): InnoDB keeps the primary key in every secondary index, and
that key is the row's row-id. On non-covering plans InnoDB looks each id up in the clustered PRIMARY tree,
from its root page, to get the other columns, one entry at a time.
Those lookups are the expensive part: one per matching row. If the index is covering, the rows come straight from the entries and the clustered index is never read.