8.2.1.6 Index Condition Pushdown Optimization

Index Condition Pushdown (ICP) is an optimization for the case
where MySQL retrieves rows from a table using an index.
Without ICP, the storage engine traverses the index to locate
rows in the base table and returns them to the MySQL server
which evaluates the WHERE condition for the
rows. With ICP enabled, and if parts of the
WHERE condition can be evaluated by using
only fields from the index, the MySQL server pushes this part
of the WHERE condition down to the storage
engine. The storage engine then evaluates the pushed index
condition by using the index entry and only if this is
satisfied is the row read from the table. ICP can reduce the
number of times the storage engine must access the base table
and the number of times the MySQL server must access the
storage engine.

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. This strategy
can be used for InnoDB and
MyISAM tables. (Note that index
condition pushdown is not supported with partitioned tables in
MySQL 5.6; this issue is resolved in MySQL 5.7.) For
InnoDB tables, however, ICP is used only
for secondary indexes. The goal of ICP is to reduce the number
of full-record reads and thereby reduce IO operations. For
InnoDB clustered indexes, the complete
record is already read into the InnoDB
buffer. Using ICP in this case does not reduce IO.

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

Get the next row, first by reading the index tuple, and
then by using the index tuple to locate and read the full
table row.

Test the part of the WHERE condition
that applies to this table. Accept or reject the row based
on the test result.

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

Get the next row's index tuple (but not the full table
row).

Test the part of the WHERE condition
that applies to this table and can be checked using only
index columns. If the condition is not satisfied, proceed
to the index tuple for the next row.

If the condition is satisfied, use the index tuple to
locate and read the full table row.

Test the remaining part of the WHERE
condition that applies to this table. Accept or reject the
row based on the test result.

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