How Index Pushdown Cuts Unnecessary Table Lookups in MySQL

Table Lookup (回表操作)

In MySQL any query that uses a secondary index must retrieve the full row from the primary key after locating the matching key. This two‑step process is called a table lookup.

Primary Key Index

In InnoDB the primary key is stored as a clustered B+‑tree. Internal nodes contain only the key values; leaf nodes store the complete row.

Secondary (Non‑Primary) Index

Secondary indexes are also B+‑trees, but their leaf nodes store only the primary‑key value of each row.

Table Lookup Process

When a query searches a secondary index, MySQL first finds the primary‑key value in the leaf node, then uses that value to fetch the full row from the primary‑key index.

Behavior in MySQL < 5.6 (no index pushdown)

Before MySQL 5.6 each row that satisfied the secondary‑index predicate required a table lookup, causing an extra tree traversal for every matching row.

Behavior in MySQL ≥ 5.6 (index pushdown)

From MySQL 5.6 onward the engine can evaluate additional predicates that are stored in the secondary index (e.g., sex = 1) while scanning the index. Only rows that satisfy those predicates proceed to the table‑lookup step, reducing the number of lookups.

Illustrative Example

Consider a table user with columns id (auto‑increment primary key), name, sex, height, age. A composite index (name, sex) exists. To find all male users named “王” the SQL is:

SELECT * FROM user WHERE name = '王' AND sex = 1;

Using the left‑most prefix rule, the secondary index is first scanned for name='王'. The leaf nodes return the primary‑key values. In MySQL < 5.6 each primary‑key lookup is performed, then the sex condition is checked, resulting in many table lookups.

With index pushdown (MySQL ≥ 5.6) the engine applies the sex = 1 filter while scanning the secondary index, so only rows that satisfy both conditions are fetched from the primary‑key index, halving the number of lookups in this example.

Summary

Table lookup is required when the needed column is not stored in the secondary index leaf; the engine must fetch the full row via the primary key.

Index pushdown (available from MySQL 5.6) reduces unnecessary lookups by filtering rows using columns present in the secondary index before performing the table lookup.