Two trees, two hops
A secondary index in InnoDB is its own B plus tree keyed on the indexed columns. Its leaf entries do not hold the full row. Instead each entry stores the primary key value of the matching row.
The bookmark lookup
To return columns not in the index, InnoDB performs a second step:
- It finds the entry in the secondary index, getting the primary key.
- It then walks the clustered index with that primary key to fetch the full row.
This second hop is called a bookmark lookup or row lookup. For a query that touches many rows, the extra hops can dominate cost.
Covering indexes
If every column a query needs is present in the secondary index, InnoDB can answer entirely from that index and skip the clustered lookup. This is a covering index, and the optimizer reports it as Using index in the EXPLAIN output.
Practical tuning
- Add columns to an index so it covers a hot query.
- Avoid selecting wide columns when only a few are needed.
Key idea
A secondary index stores primary key values, so non covered queries do a second clustered index hop per row, while a covering index answers from the secondary tree alone.