Understanding Index Condition Pushdown (ICP) in MySQL: Theory, Usage, and Performance Comparison

Index Condition Pushdown (ICP)

Index Condition Pushdown (ICP) is a technique introduced in MySQL 5.6 to improve query efficiency and reduce the number of row‑lookups (back‑table operations). It allows the server to push part of the WHERE clause down to the storage engine, so fewer rows need to be read from the base table, lowering I/O overhead and speeding up queries.

Note: ICP works only with composite (multi‑column) indexes and moves filtering that would normally be done at the server layer into the storage engine.

In simple terms, instead of using a secondary index to locate the primary‑key ID, then fetching the full row and applying the WHERE filter, ICP lets the secondary index filter rows directly before the back‑table lookup, reducing the number of back‑table accesses.

Practical Demonstration

ICP is a new feature in MySQL 5.6 that enables index‑level filtering. Without ICP, the storage engine traverses the index to locate rows, returns them to the server, and the server evaluates the WHERE clause. With ICP enabled, if part of the WHERE clause can be evaluated using only index columns, the server pushes that condition to the storage engine, which filters rows using the index before returning them.

Benefit: ICP reduces the number of times the storage engine must access the base table and the number of times the server must access the storage engine.

Limitation: The acceleration effect depends on the proportion of rows filtered out by the index condition inside the storage engine.

Enabling/Disabling ICP

ICP is enabled by default. It can be controlled with the optimizer_switch system variable:

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

When ICP is active, the EXPLAIN output shows Using index condition in the Extra column.

ICP Usage Example

Creating a test table:

CREATE TABLE `people` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `zipcode` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `firstname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `lastname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `address` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zip_last_first` (zipcode, lastname, firstname)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

Inserting sample data:

INSERT INTO `people` VALUES
('1','000001','三','张','北京市'),
('2','000002','四','李','南京市'),
('3','000003','五','王','上海市'),
('4','000001','六','赵','天津市');

Query using the composite index:

SELECT * FROM people
WHERE zipcode='000001'
  AND lastname LIKE '%张%'
  AND address LIKE '%北京市%';

The EXPLAIN output shows Using index condition , indicating that ICP is in effect. The Using where flag appears because the address condition is not covered by the index and must be evaluated on the server side.

Performance Comparison

A stored procedure inserts a large number of rows with zipcode='000001' to create a sizable dataset. Profiling is enabled with:

SET profiling=1;

Two queries are executed:

-- With ICP (default)
SELECT * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%';

-- Without ICP (hint)
SELECT /*+ no_icp (people) */ * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%';

After running SHOW PROFILES\G , the timing results show that the query with ICP consistently runs faster, especially when the dataset is large.

Scanning Process Before and After ICP

Without ICP:

Storage layer: Returns all rows that satisfy the index key, then the server applies the remaining WHERE filters.

Server layer: Performs final filtering on the rows received from storage.

With ICP:

Storage layer: Determines the index key range, applies the index filter, and only returns rows that satisfy the filter; rows failing the filter are discarded without a back‑table lookup.

Server layer: Applies any remaining table‑level filters (e.g., non‑index columns).

The cost reduction comes from eliminating the back‑table reads for rows filtered out by the index condition.

Conditions for Using ICP

Applicable when the access type reported by EXPLAIN is range , ref , eq_ref , or ref_or_null .

Supported storage engines: InnoDB and MyISAM (including partitioned tables).

Only works with secondary indexes; the goal is to reduce full‑row reads.

Not applicable when a covering index is used, because no back‑table lookup is needed.

Conditions in correlated subqueries cannot use ICP.

References

Original article: https://juejin.cn/post/7439649869343522870