Documentation Home
MySQL 8.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.2Mb
PDF (A4) - 40.3Mb
Man Pages (TGZ) - 262.0Kb
Man Pages (Zip) - 367.6Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 8.4 Reference Manual  / ...  / Optimization  / Optimizing SQL Statements  / Optimizing SELECT Statements  /  Index Condition Pushdown Optimization

10.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 theWHERE condition for the rows. With ICP enabled, and if parts of theWHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of theWHERE 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.

Applicability of the Index Condition Pushdown optimization is subject to these conditions:

  • ICP is used for therange,ref,eq_ref, andref_or_null access methods when there is a need to access full table rows.

  • ICP can be used forInnoDB andMyISAM tables, including partitionedInnoDB andMyISAM tables.

  • ForInnoDB tables, ICP is used only for secondary indexes. The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. ForInnoDB clustered indexes, the complete record is already read into theInnoDB buffer. Using ICP in this case does not reduce I/O.

  • ICP is not supported with secondary indexes created on virtual generated columns.InnoDB supports secondary indexes on virtual generated columns.

  • Conditions that refer to subqueries cannot be pushed down.

  • Conditions that refer to stored functions cannot be pushed down. Storage engines cannot invoke stored functions.

  • Triggered conditions cannot be pushed down. (For information about triggered conditions, seeSection 10.2.2.3, “Optimizing Subqueries with the EXISTS Strategy”.)

  • Conditions cannot be pushed down to derived tables containing references to system variables.

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

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

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

Using Index Condition Pushdown, the scan proceeds like this instead:

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

  2. Test the part of theWHERE 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.

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

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

EXPLAIN output showsUsing index condition in theExtra column when Index Condition Pushdown is used. It does not showUsing index because that does not apply when full table rows must be read.

Suppose that a table contains information about people and their addresses and that the table has an index defined asINDEX (zipcode, lastname, firstname). If we know a person'szipcode 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 withzipcode='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 people who havezipcode='95054'.

With Index Condition Pushdown, MySQL checks thelastname LIKE '%etrunia%' part before reading the full table row. This avoids reading full rows corresponding to index tuples that match thezipcode condition but not thelastname condition.

Index Condition Pushdown is enabled by default. It can be controlled with theoptimizer_switch system variable by setting theindex_condition_pushdown flag:

SET optimizer_switch = 'index_condition_pushdown=off';SET optimizer_switch = 'index_condition_pushdown=on';

SeeSection 10.9.2, “Switchable Optimizations”.