Index Condition Pushdown optimization is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows. The optimization is that the server tries to use index information to defer (“push down”) reading of full table rows unless it is known to be necessary. This is done by performing early tests on index tuples first. This strategy can be used for MyISAM tables.

To see how this optimization works, consider first how an index scan proceeds when Index Condition Pushdown is not used:

When Index Condition Pushdown is used, the scan proceeds like this instead:

When Index Condition Pushdown is used, the Extra column in EXPLAIN output shows Using index condition. It will not show Index only because that does not apply when full table rows must be read.

Suppose that we have a table containing information about people and their addresses and that the table has an index defined as INDEX (zipcode, lastname, firstname). If we know a person's zipcode value but are not sure about the last name, we can search like this:

SELECT * FROM people 
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%'

MySQL can use the index to scan through people with zipcode='95054'. The second part (lastname LIKE '%etrunia%') cannot be used to limit the number of records that must be scanned, so without Index Condition Pushdown, this query must retrieve full table rows for all the people who have zipcode='95054'.

With Index Condition Pushdown, MySQL will check the lastname LIKE '%etrunia%' part before reading the full table row. This avoids reading full rows corresponding to all index tuples that do not match the lastname condition. The full-row test is “pushed down” to take place later than the index-only test.

Index Condition Pushdown is enabled by default; it can be controlled with the engine_condition_pushdown system variable. This variable also controls table Condition Pushdown as used for NDB; see Section 7.2.7, “Condition Pushdown Optimization”.