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
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
Index Condition Pushdown optimization is used for the
ref_or_null access methods
when there is a need to access full table rows. This strategy
can be used for
MyISAM tables. Beginning with
MySQL 5.7.3, it can also be used with partitioned
(Bug #17306882, Bug #70001). For
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
clustered indexes, the complete record is already read into
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
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
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
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
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
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
Index Condition Pushdown is enabled by default; it can be
controlled with the
variable by setting the
index_condition_pushdown flag. See
Section 188.8.131.52, “Controlling Switchable Optimizations”.