Mastering MySQL Index Pushdown and Table Lookup: From Basics to High‑Version Optimizations

Table Lookup (回表操作)

In InnoDB every index is stored as a B+‑tree. For the primary‑key index the leaf nodes contain the full row, while internal nodes store only the primary‑key values. For a secondary index the leaf nodes store the indexed columns together with the primary‑key value; the full row is not stored.

When a query uses a secondary index, MySQL first reads the matching leaf entries, obtains the primary‑key, and then performs a second lookup in the primary‑key B+‑tree to retrieve the complete row. This two‑step process is called a table lookup (回表).

Primary‑Key Index Structure

The primary‑key index is a clustered B+‑tree. The leaf pages store the entire row (all column values) and are ordered by the primary‑key. Internal pages contain only the primary‑key values to guide the search.

Secondary‑Index Structure

A secondary (non‑primary) index is also a B+‑tree, but its leaf pages store the indexed column values plus the primary‑key of the row. The full row data is not present in the leaf.

Behavior in MySQL < 5.6 (no Index Condition Pushdown)

Before MySQL 5.6 the optimizer could not evaluate additional predicates on a secondary index. After locating the primary‑key from the secondary index, it always fetched the full row and then applied the remaining WHERE conditions.

Example schema:

CREATE TABLE user (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32),
    sex  TINYINT,
    height INT,
    age   INT,
    INDEX idx_name_sex (name, sex)
);

Query to find male users named “王”:

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

Execution steps in MySQL 5.6‑:

Scan the secondary index idx_name_sex for the left‑most prefix name='王'.

For each matching leaf entry retrieve the primary‑key id.

Use the primary‑key to fetch the full row from the primary‑key index.

Apply the remaining predicate sex = 1 to the fetched row.

If many rows share the same name, the number of table lookups grows proportionally, causing noticeable latency.

Behavior in MySQL ≥ 5.6 (Index Condition Pushdown)

Starting with MySQL 5.6 the optimizer supports Index Condition Pushdown (ICP). The engine evaluates additional predicates directly on the secondary index, so a row is fetched from the primary index only when it already satisfies those predicates.

In the previous example the condition sex = 1 can be pushed down because the secondary index stores the sex column. The execution becomes:

Scan idx_name_sex for entries where name='王' and sex=1.

Only the matching leaf entries trigger a primary‑key lookup.

This reduces the number of table lookups, often by half or more, especially when the pushed‑down predicate filters out a large fraction of rows.

Key Takeaways

Table lookup is required whenever the needed column is not stored in the secondary‑index leaf; the primary‑key from the leaf is used to fetch the full row from the primary‑key index.

Index Condition Pushdown (available from MySQL 5.6) lets the optimizer apply additional WHERE clauses on the secondary index, thereby skipping unnecessary primary‑key lookups and improving query performance.