Index Condition Pushdown (ICP) Optimization in MySQL

Index Condition Pushdown (ICP) is a query‑optimization technique for MySQL that, when enabled, pushes the portion of a WHERE clause that can be evaluated using index columns down to the storage‑engine layer. The engine then filters rows using the index entries and reads the full row from the base table only when the remaining predicates are satisfied, thereby reducing the number of base‑table accesses and the interaction between the MySQL server and the storage engine.

The core idea is to move part of the query conditions from the MySQL server layer to the storage‑engine layer, allowing early data filtering during index traversal.

Applicable scenario limitations :

Supported for InnoDB and MyISAM tables (including partitioned tables).

InnoDB tables are limited to secondary indexes (clustered indexes already cache full rows and gain no benefit).

Does not support secondary indexes on virtual generated columns.

Does not support conditions that contain subqueries, stored functions, trigger‑related conditions, or derived tables that reference system variables.

Optimization principle comparison :

When ICP is not enabled : Retrieve the next row tuple via the index. Locate and read the full data row. Apply the complete WHERE filter. Accept or reject the row.

When ICP is enabled : Fetch the next index tuple (no full‑row read). Evaluate only the predicate parts that can be satisfied by the index columns. If the condition fails, skip the row. If the condition passes, read the full data row. Apply the remaining WHERE predicates.

In the EXPLAIN output, using ICP adds Using index condition in the Extra column; a full‑table scan shows Using index (ICP not involved).

Typical application scenario :

Assume a table storing people’s address information with an index on (zipcode, lastname, firstname) . The query:

SELECT * FROM people
  WHERE zipcode='95054'
    AND lastname LIKE '%etrunia%'
    AND address LIKE '%Main Street%';

MySQL first uses the index to locate all rows with zipcode='95054' , then the storage engine directly filters the lastname condition without a back‑row lookup, and finally reads the full rows only for records that satisfy both conditions, avoiding a full scan for the lastname predicate.

Configuration method :

ICP is enabled by default. It can be toggled via the optimizer_switch system variable:

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

Translated from: https://dev.mysql.com/doc/refman/8.4/en/index-condition-pushdown-optimization.html