Index Condition Pushdown — filtering inside InnoDBMySQL 5.6+ • MariaDB 5.3+

See how ICP checks trailing index columns before fetching the row, saving unnecessary clustered-index lookups.

Lesson familyIndex Access

Parameters (Index Condition Pushdown)

Rows matching the leading index condition (last_name LIKE 'S%').
Percentage of rows also matching the pushed condition (first_name LIKE 'J%').

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

  • Range scan first finds entries by last_name LIKE 'S%' in the secondary index.
  • Without ICP, every entry triggers clustered-row fetch, then first_name predicate is tested.
  • With ICP, first_name predicate is tested on index payload before fetch.
  • Only entries that pass predicate trigger clustered-row reads in ICP path.
  • Watch fetch counters diverge to see saved random I/O.
Ready — press Play
0.0s 0.0s

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 uses a range, ref, or eq_ref access type on a composite index.

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 table is InnoDB or MyISAM (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 will show Using index condition instead of the usual Using where.

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".