How Index Condition Pushdown Supercharges MySQL Query Performance

Simple Explanation

Index Condition Pushdown (ICP) moves part of the WHERE clause into the storage‑engine index scan. Rows that do not satisfy the pushed‑down predicates are filtered out before they reach the server layer, reducing I/O and CPU usage.

Traditional Index Scan

Without ICP, MySQL uses the index to locate rows that satisfy some predicates, then returns all matching rows to the server layer where the remaining predicates are evaluated. This causes unnecessary data movement.

Query Flow with ICP

With ICP the storage engine applies additional filter conditions during the index scan, returning only rows that satisfy all pushed‑down predicates.

Example

Assume a table products with a composite index (category_id, price) and the query:

SELECT * FROM products WHERE category_id = 10 AND price < 100;

Without Index Condition Pushdown

The storage layer uses the index to locate rows where category_id = 10.

All matching rows are sent to the server layer.

The server layer filters those rows with price < 100.

If many rows have category_id = 10, a large amount of data is transferred, increasing I/O and CPU load.

With Index Condition Pushdown

The storage layer uses the same index but also evaluates price < 100 while scanning.

Only rows that satisfy both category_id = 10 and price < 100 are returned to the server layer.

The volume of data transferred is dramatically reduced, improving query performance.

Advantages

Reduced I/O overhead : Fewer rows are read from disk and sent across layers.

Faster query execution : The server performs less secondary filtering.

No SQL changes required : ICP is an engine‑level optimization that works transparently.

Usage Considerations

ICP is supported only by storage engines that implement it; in MySQL, only InnoDB provides ICP.

The performance gain depends on query complexity, data distribution, and index selectivity. It is not guaranteed for every query.

How to Verify ICP

Run EXPLAIN on the query. If ICP is used, the Extra column contains the phrase “Using index condition”. Example:

EXPLAIN SELECT * FROM products WHERE category_id = 10 AND price < 100;

Typical output snippet: Extra: Using index condition If the phrase is absent, ICP is not active for that query.