Understanding MySQL Table Lookups and Index Condition Pushdown

1. Table Lookup (回表)

1.1 Concept

When using MySQL we often hear the terms “table lookup” (回表) and “index condition pushdown”. A table lookup occurs when the storage engine first uses a secondary index to locate primary key values and then reads the full rows from the primary key index.

CREATE TABLE `test_temp` (
  `id` INT(11) NOT NULL DEFAULT '0',
  `a` VARCHAR(20) DEFAULT NULL,
  `b` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY (`b`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

We insert four rows into test_temp and create a normal index on column b. The query SELECT * FROM test_temp WHERE b IN (10,20,30,40); triggers three table lookups because the engine must fetch the full rows for the matching primary keys (ids 400, 300, 200).

1.2 Drawbacks

Frequent table lookups increase disk I/O and can invalidate cache entries, leading to slower query performance.

1.3 Mitigation Measures

Two common ways to avoid table lookups are:

Covering index : Query only the indexed columns, e.g. SELECT b, id FROM test_temp WHERE b IN (...);, so the engine can return results directly from the secondary index.

Query only necessary fields : Reduce the selected columns to those contained in the index or create a composite index that includes all required columns.

2. Index Condition Pushdown (索引下推)

MySQL’s logical architecture consists of the Server layer (query parsing, optimization, caching, built‑in functions) and the Storage Engine layer (actual data storage and retrieval). Index condition pushdown allows the storage engine to apply additional filter conditions using the secondary index, reducing the number of rows that need to be fetched from the primary key index.

Example table (same as above) with a composite index a_b on columns a and b:

CREATE TABLE `test_temp` (
  `id` INT(11) NOT NULL DEFAULT '0',
  `a` VARCHAR(20) DEFAULT NULL,
  `b` VARCHAR(10) DEFAULT NULL,
  `c` VARCHAR(10) DEFAULT NULL,
  `d` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a_b` (`a`,`b`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

After inserting data, the statement SELECT * FROM test_temp WHERE a > '10' AND b < '50'; produces an execution plan that shows “Using index condition”, indicating that index condition pushdown is active.

With pushdown, the storage engine filters rows using the composite index before any table lookup, dramatically reducing I/O and improving performance.

Limitations

Applicable mainly to eq_ref, range, ref, and ref_or_null access types.

Supported by InnoDB, MyISAM, and partitioned tables.

Only secondary indexes can use pushdown; the primary (clustered) index does not.

Conditions inside subqueries are not eligible for pushdown.

Stored functions in the WHERE clause prevent pushdown because the engine cannot evaluate them.

When the second column of a composite index is not used for filtering (e.g., querying on column c instead of b), the optimizer may still report pushdown, but the engine must perform a table lookup for each matching primary key.

Summary

The article introduced MySQL’s table lookup and index condition pushdown mechanisms, explained their impact on query performance, and offered practical advice for reducing costly lookups in real‑world applications.