Parameters (Index Condition Pushdown)
About this exampleAbout this example
Example query this animation executes
-- Composite index on (last_name, first_name) SELECT * FROM employees WHERE last_name LIKE 'S%' AND first_name LIKE 'J%';
Without ICP: range scan on last_name and fetch every row before checking first_name. With ICP: InnoDB evaluates first_name on index entries first, so many clustered-row fetches are skipped.
What you'll see in the animation
- Each row of the two panels is one entry of the (last_name, first_name) index that matches last_name LIKE 'S%'. The entry holds both names and the primary key id, not the rest of the row.
- Left, without ICP: every entry's row is read from the clustered index (red), then the server tests first_name LIKE 'J%' and throws away the rows that fail.
- Right, with ICP: InnoDB tests first_name on the entry itself (blue) and reads the row only when it starts with J (green), so the other entries cost no row read.
- The counters under each panel show the row reads so far; watch them diverge.
- Next step handles one entry on both sides, and the simplified code beside the drawing highlights the lines each step runs.
Cost readout (Index Condition Pushdown)
Index rows scanned ?Total rows matching the range condition on the leading index column. All of these would need a row fetch without ICP.
—
Row fetches without ICP ?Without ICP, every index row triggers a clustered-index lookup to fetch the full row, then the server filters.
—
Row fetches with ICP ?With ICP, only rows matching the pushed condition on trailing index columns trigger a clustered-index fetch.
—
Row fetches saved ?The difference: how many unnecessary clustered-index lookups ICP eliminates by checking the condition at the index level.
—
Row fetches vs index scan size (log–log, selectivity fixed)
Learn more — when does ICP activate?
Index Condition Pushdown was introduced in MySQL 5.6 and is enabled by
default (optimizer_switch='index_condition_pushdown=on').
ICP applies when:
1. The query reads through a secondary index with a range scan or a lookup (range, ref, eq_ref or ref_or_null). It is not used for a const lookup, and InnoDB does not use it on the clustered primary key, where the whole row is already in hand.
2. There are additional WHERE conditions that reference columns present in the index but not used by the access method (e.g. trailing columns of a composite index, or conditions that can't form a range but can be checked against the index entry).
3. The engine supports it: InnoDB or MyISAM in MySQL; also Aria and MyRocks in MariaDB. InnoDB benefits most because avoiding a clustered-index lookup is expensive: it's a random I/O for non-covering indexes.
You can see ICP in action in EXPLAIN output: the
Extra column shows Using index condition
(with Using where next to it when part of the condition
still runs in the server).
ICP does not help covering indexes (the row fetch is already avoided) or full table scans (there's no index to push to).
Sources: MySQL 8.4 reference manual §10.2.1.6 "Index Condition Pushdown Optimization"; MariaDB Knowledge Base "Index Condition Pushdown".