Filtering Earlier
Index condition pushdown, often shortened to ICP, lets the storage engine evaluate parts of the WHERE clause while it is still scanning the index, before fetching the full row from the table.
The Problem It Solves
Without pushdown the engine uses only the leading indexed columns to find candidate entries, then performs a table lookup for each one and applies the remaining conditions on the full row. Many of those rows are discarded after the expensive lookup.
With pushdown the engine applies any condition whose columns are present in the index during the scan itself. Rows failing the test are rejected before the lookup happens, so the random reads are saved.
A Concrete Case
Consider an index on last name and first name with a query filtering last name by range and first name by a pattern. The range uses the index for seeking; the first name pattern can be checked inside the index, dropping non matches before any table access.
Key idea
Index condition pushdown evaluates index resident filter columns during the scan, rejecting rows before the costly table lookup instead of after.