Understanding MySQL Index Condition Pushdown (ICP) and Its Performance Benefits

Index Condition Pushdown (ICP) is an optimization introduced in MySQL 5.6 that pushes index filter conditions down to the storage engine, reducing the number of base‑table accesses and the amount of data transferred to the MySQL server layer. ICP works for both MyISAM and InnoDB, and this article focuses on InnoDB.

Key components involved in MySQL ICP:

MySQL server layer: parses SQL, generates execution plans, and performs secondary filtering.

Storage engine layer: retrieves rows via index or full‑table scans and returns them to the server.

Index scan: uses index keys to locate primary‑key values, then fetches rows.

Index filter: applies additional conditions after the index scan before a table lookup.

ICP merges index scanning and index filtering, pushing the filtered rows down to the storage engine, which yields two main benefits: fewer table‑row lookups (reduced “back‑table” operations) and less data uploaded to the server.

ICP is enabled by default and can be disabled with the optimizer switch optimizer_switch='index_condition_pushdown=off' or via a query hint.

Process without ICP

The storage engine returns every row that matches the index key, and the server layer applies the remaining WHERE conditions after all rows are fetched.

(localhost:mysqld.sock)|(ytt)>select * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
id: 28965
f1: 81
f2: 89
f3: 100
f4: 35
r1: 1
r2: 12844bda dog 11ea a051 08002753f58d
r3: 17
r4: 5
1 row in set (0.00 sec)

Process with ICP

When ICP is enabled, the storage engine first narrows the index range using the indexed columns, then applies additional indexed conditions within that range, returning only rows that satisfy all conditions. The server layer only needs to filter non‑indexed predicates.

Performance diagrams (omitted) clearly show that ICP reduces the number of rows transferred between the storage engine and the server, saving I/O.

To verify whether ICP is used, run EXPLAIN and look for Using index condition in the Extra column.

(localhost:mysqld.sock)|(ytt)>explain select /*+ no_icp (t1)  */ * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: idx_r4,idx_u1
key: idx_u1
key_len: 5
ref: const
rows: 325
filtered: 0.12
Extra: Using where

(localhost:mysqld.sock)|(ytt)>explain select * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: idx_r4,idx_u1
key: idx_u1
key_len: 5
ref: const
rows: 325
filtered: 0.12
Extra: Using index condition; Using where

Additional ways to observe ICP’s impact:

Run SHOW STATUS LIKE '%handler%' to compare Handler_read_next values (e.g., 325 without ICP vs. 14 with ICP).

Enable profiling and compare total query durations (e.g., 0.001019 s without ICP vs. 0.001003 s with ICP).

(localhost:mysqld.sock)|(ytt)>show status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_read_next          | 325   |
+----------------------------+-------+
... (after enabling ICP) ...
| Handler_read_next          | 14    |
+----------------------------+-------+

ICP limitations:

Applicable only to access methods that read the base table (range, ref, eq_ref, ref_or_null).

Works with partitioned tables.

Targets secondary indexes; primary‑key lookups are not pushed down.

Does not support indexes on virtual (generated) columns or function‑based indexes.

Cannot be used with conditions that reference subqueries.

For further reading on MySQL indexing strategies, see the recommended articles linked at the end of the original post.