How Index Condition Pushdown Boosts MySQL Query Performance: A 90× Speed Test

MySQL Index Types

MySQL supports two main index categories: primary key indexes, which are unique, non‑null, and limited to one per table, and ordinary (secondary) indexes, which can be created on any column and may allow duplicate or null values.

Index Data Structure

Both primary and secondary indexes use a B+Tree structure, a self‑balancing multi‑way search tree that stores ordered key‑value pairs. The diagram below illustrates a typical B+Tree.

Additional visualizations of data structures, including B+Tree generation, are available at this URL .

Secondary (Non‑Clustered) Index

In MySQL, a secondary index stores the primary key value in its leaf nodes rather than the full row data. Queries must first locate the primary key via the secondary index and then fetch the row, unlike primary key indexes that retrieve rows directly.

Index Condition Pushdown (ICP)

ICP (Index Condition Pushdown) is a feature introduced in MySQL 5.6 that pushes certain WHERE‑clause conditions down to the storage engine, allowing it to filter rows using the index before returning them to the server, thus reducing I/O.

Experiment

5.1 Database Version

<code>mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.12-log |
+------------+
1 row in set (0.00 sec)</code>

The server runs MySQL 5.7, which supports ICP.

5.2 Test Data

A table t_user with 6,000,000 rows is created:

<code>CREATE TABLE `t_user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` char(11) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `sex` tinyint(4) DEFAULT NULL,
  `age` tinyint(4) DEFAULT '0',
  `birthday` datetime DEFAULT NULL,
  `updated_time` datetime DEFAULT NULL,
  `update_time` datetime(6) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</code>

A composite index on (age, name, mobile) is added:

<code>create index idx_age_name_mobile on t_user(age, name, mobile);</code>

5.3 Checking ICP Status

<code>SHOW VARIABLES LIKE '%optimizer_switch%';</code>

The output includes index_condition_pushdown=on , meaning ICP is enabled by default.

Disabling ICP

<code>SET optimizer_switch='index_condition_pushdown=off';</code>

Run a query that uses the composite index:

<code>mysql&gt; select * from t_user t where t.age = 22 and t.name like '%东%';
... (337 rows) ...
8.95 sec</code>

Explain plan shows Using where in the Extra column.

<code>mysql&gt; explain select * from t_user t where t.age = 22 and t.name like '%东%';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+--------+----------+-------------+
| id | select_type | table | type       | ...  | possible_keys       | key                 | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+--------+----------+-------------+
| 1  | SIMPLE      | t     | ref        | ...  | idx_age_name_mobile | idx_age_name_mobile | 2       | const | 139264 | 11.11    | Using where |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+--------+----------+-------------+</code>

Enabling ICP

<code>SET optimizer_switch='index_condition_pushdown=on';</code>

Run the same query:

<code>mysql&gt; select * from t_user t where t.age = 22 and t.name like '%东%';
... (337 rows) ...
0.10 sec</code>

Explain plan now shows Using index condition in the Extra column, indicating ICP is active.

<code>mysql&gt; explain select * from t_user t where t.age = 22 and t.name like '%东%';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+--------+----------+-----------------------+
| id | select_type | table | type       | ...  | possible_keys       | key                 | key_len | ref   | rows   | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+--------+----------+-----------------------+
| 1  | SIMPLE      | t     | ref        | ...  | idx_age_name_mobile | idx_age_name_mobile | 2       | const | 139264 | 11.11    | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+--------+----------+-----------------------+</code>

Conclusion

Enabling Index Condition Pushdown allows the storage engine to filter rows using the index before fetching full rows, dramatically reducing I/O. In this test, the query time dropped from 8.95 seconds to 0.10 seconds—a roughly 90‑fold improvement.