MySQL Index Condition Pushdown: Why Your Index Scan Still Reads Too Many Rows

The index is there, but the I/O is still high

You add an index, EXPLAIN shows it in the key column, and the query still takes too long. The server examines far more rows than you expected, and disk I/O climbs even though the index should be narrowing the search. The problem is often that the index limits the range of the scan, but MySQL still fetches every full row in that range before applying the rest of your WHERE clause.

Index Condition Pushdown (ICPD) is the optimization designed to fix exactly that. Since MySQL 5.6 it is enabled by default, and in the right circumstances it lets InnoDB discard unwanted rows while they are still sitting in the index, before the expensive full-row lookup happens.

What ICPD changes inside InnoDB

To see why ICPD matters, picture the normal path for a secondary index scan. InnoDB reads matching entries from the index leaf pages—the lowest level of the B-tree structure. Each entry contains the indexed columns plus the primary key value. The storage engine then uses that primary key to fetch the complete row from the clustered index, the table's primary data structure. Only after that full row is in memory does the server apply any remaining WHERE conditions.

ICPD moves part of that filtering earlier. If your index contains columns beyond the ones used to set the scan boundaries, InnoDB can evaluate those extra conditions directly on the leaf page. Rows that fail are skipped entirely; only survivors trigger a clustered-index lookup. On wide tables with selective filters, this can remove a large amount of unnecessary I/O.

The keyword is can. ICPD only applies to predicates on columns that exist in the index being scanned. If a condition references a column that is not in the index, the leaf page does not contain the data needed to evaluate it, so the condition cannot be pushed down.

How to verify ICPD is working

Run EXPLAIN from any MySQL client with SELECT privilege on the target table. In MySQL 8.0 you can also use EXPLAIN FORMAT=TREE for a more readable plan. Look at the Extra column. When ICPD is active for a query, you will see the text Using index condition. If you do not see it, the optimizer either found no pushable predicates or decided pushdown was not cost-effective.

You can force a comparison by toggling the index_condition_pushdown flag in the optimizer_switch system variable. The safest place to experiment is your current session:

SET SESSION optimizer_switch='index_condition_pushdown=off';
EXPLAIN SELECT ...;
SET SESSION optimizer_switch='index_condition_pushdown=on';
EXPLAIN SELECT ...;

Changing the variable globally with SET GLOBAL requires elevated privileges and affects every new connection, so prefer session-level testing unless you intend a server-wide change. If you do change it globally and need to revert, set the flag back to default or on.

A concrete example

Suppose you have an events table with a wide payload column and an index on (account_id, event_type, occurred_at). You run:

SELECT * FROM events
WHERE account_id = 42
  AND event_type = 'login'
  AND occurred_at > '2025-01-01';

The optimizer uses account_id and event_type to set the index range. Because occurred_at is also in the index, InnoDB can evaluate the date condition on the leaf pages and skip older entries before fetching full rows. In EXPLAIN, you should look for Using index condition in the Extra column to confirm the pushdown happened.

Now change the last predicate to a JSON expression:

SELECT * FROM events
WHERE account_id = 42
  AND event_type = 'login'
  AND JSON_EXTRACT(metadata, '$.region') = 'EU';

Even though the first two predicates are pushable, the JSON function typically prevents that condition from being pushed to the storage engine. InnoDB must fetch the full row to evaluate it, and the Using index condition note may disappear or apply to fewer predicates. The same limitation commonly affects user-defined functions, stored routines, and certain spatial predicates.

Limitations and trade-offs

ICPD is not a universal win. Because it filters rows earlier, it can change the optimizer's cardinality estimates—its prediction of how many rows a step will return. In complex multi-table joins, aggressive pushdown can make one table look more selective than it really is, leading the optimizer to choose a suboptimal join order. If a query regresses after a schema or statistics change, compare plans with the flag on and off.

MySQL 8.0 improved ICPD with better cost-based decisions and extended subquery pushdown, but the fundamental rule remains: the predicate must reference columns available in the index leaf page. If your selective filter lives in a column outside the index, ICPD cannot help, and you may need a composite index or a generated column to bring that data into the index structure.

Actionable next step

Pick your slowest index-scan query. Run EXPLAIN and check whether Extra contains Using index condition. If it does not, inspect your WHERE clause: are the non-range predicates on columns inside the index? If they are not, consider widening the index or adding a generated column. If they are, toggle index_condition_pushdown in your session and compare the row estimates and join order. That single check tells you whether InnoDB is filtering early or fetching full rows only to throw them away.