Understanding MySQL Index Pushdown and Table Lookup: From Basics to High-Version Optimizations

Table Lookup (回表操作)

Any query that uses an index may eventually need to fetch the full row from the primary‑key index; this two‑step process is called a table‑lookup or “回表”.

Primary Key Index

In InnoDB the primary key is stored as a clustered B+‑tree; internal nodes hold only the primary‑key values while leaf nodes contain the complete row data.

Non‑Primary (Secondary) Index

Secondary indexes also use a B+‑tree, but their leaf nodes store only the primary‑key value of the row, not the full row.

How Table Lookup Works

When a query searches a secondary index, MySQL first finds the matching leaf entry, obtains the primary‑key value, then searches the primary‑key B+‑tree to retrieve the full row. This two‑step process is the table‑lookup operation.

Low‑Version Behavior (MySQL 5.5 and Earlier)

Before MySQL 5.6 there was no index‑pushdown optimization. Every matching row required a table‑lookup, increasing the number of B+‑tree traversals and degrading performance for large result sets.

Example Query (User Table)

Consider a user table with columns id (auto‑increment primary key), name, sex (1 = male, 2 = female), and other attributes. To find all male users named “王”, the SQL is straightforward:

The execution principle follows the left‑most prefix rule of the composite index: MySQL first locates matching entries in the secondary index, retrieves the primary‑key values, then looks up the full rows in the primary‑key tree and finally checks the gender condition.

High‑Version Behavior (MySQL 5.6+ – Index Pushdown)

From MySQL 5.6 onward the engine can evaluate additional predicates on the secondary‑index leaf before performing the table‑lookup. Only rows that satisfy all conditions (e.g., sex = 1) trigger a lookup, reducing the number of lookups dramatically (e.g., from four to two in the example).

Summary

Table‑lookup is required when the needed column is not stored in the secondary index; the engine must fetch the primary‑key value and then retrieve the full row.

Index pushdown (索引下推) reduces unnecessary lookups by filtering rows using index‑stored columns before accessing the primary‑key tree, improving query performance.