Master MySQL Table Lookups and Index Condition Pushdown

Hello, I am Su San.

1. Table Lookup (回表)

1.1 Concept

We create a table test_temp with a primary key id and a secondary index on column b. Four rows are inserted.

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;
INSERT INTO test_temp(`id`,`a`,`b`) VALUES
(100,10,50),
(200,20,40),
(300,30,30),
(400,40,10);

The query SELECT * FROM test_temp WHERE b IN (10,20,30,40); triggers the following steps (illustrated in the diagram):

1. From index b find id 400 for value 10, then fetch the row from the primary key index.

2. Value 20 not found, continue.

3. Value 30 finds id 300, fetch row.

4. Value 40 finds id 200, fetch row.

5. Return result set to client.

Steps 1, 3, and 5 involve a table lookup; the query performed three table lookups.

1.2 Drawbacks

Frequent table lookups increase disk I/O and can invalidate cache, hurting performance.

1.3 Mitigation

Two ways to avoid table lookups:

Covering index: query only indexed columns, e.g. SELECT b, id FROM test_temp WHERE b IN (...); or create a covering index on b and a.

Select only necessary fields: limit the query to columns present in the index or add a composite index that includes required columns.

2. Index Condition Pushdown (索引下推)

MySQL’s logical architecture consists of the Server layer (query parsing, analysis, optimization, caching, built‑in functions) and the storage engine layer (data storage and retrieval).

We create a table with a composite index a_b on columns a and b and insert data.

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;
INSERT INTO test_temp(`id`,`a`,`b`,`c`,`d`) VALUES
(100,10,20,2,1),
(200,10,40,4,2),
(300,10,30,3,3),
(400,40,10,1,4);

Running EXPLAIN SELECT * FROM test_temp WHERE a > '10' AND b < '50'; shows the “Using index condition” flag, indicating index condition pushdown.

With index condition pushdown, the storage engine uses the composite index to filter rows on both a and b before any table lookup, reducing I/O and improving performance.

Index condition pushdown was introduced in MySQL 5.6; earlier versions do not support it.

Limitations:

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

Supported by InnoDB, MyISAM, and partitioned tables.

Only secondary indexes support pushdown; primary (clustered) indexes do not.

Conditions in subqueries or stored functions are not pushed down.

Changing the second condition to column c ( WHERE a > '10' AND c < '50') cannot use pushdown because the second column is not part of the composite index, even though EXPLAIN may still show the flag.

Conclusion

This article introduced MySQL’s table lookup and index condition pushdown, two crucial concepts for database performance tuning and interview preparation.