Index Condition pushdown optimization

MySQL 5.6 Reference Manual/Index Condition pushdown optimization 8.2.1.6 Index Condition pushdown optimization

Index Condition pushdown (ICP) is a optimization for the case where MySQL retrieves rows from a table using a 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 is evaluated by using only to 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 ' 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 the MySQL server Must access the storage engine.

Index Condition pushdown optimization is used to the range, ref, EQ_REF, and ref_or_null access methods when there is a n Eed to access full table rows. This strategy can is used for InnoDB and MyISAM tables. (Note that the index condition Pushdown is isn't supported with partitioned tables in MySQL 5.6; This issue be resolved in MySQL 5.7.) for InnoDB tables, however, ICP are 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 the case does not reduce IO.

To-optimization works, consider-a index scan proceeds when index Condition pushdown are not used:

Get the next row, the index tuple of the reading, and then by using the "index tuple to locate" and read the full table ro W.

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

When Index Condition pushdown are 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 so applies to this table and can is checked using only index columns. If the condition is isn't 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" applies to this table. Accept or reject the row based on the test result.

When index Condition pushdown are used, the Extra column in EXPLAIN output shows Using Index Condition. It won't show Index only because which does not apply to full table rows must is read.

Suppose that we have a table containing information about people and their addresses and then the table has an index Defin Ed 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 '" Like
  '%main street% ';

MySQL can use the index to scan through people with zipcode= ' 95054 '. The second part (LastName like '%etrunia% ') cannot is used to limit the number of rows that must is scanned, so without in Dex Condition Pushdown, this query must retrieve full table rows to the people who havezipcode= ' 95054 '.

With the Index Condition pushdown, the MySQL'll check the LastName like '%etrunia% ' part before reading the full table row. This avoids reading full rows corresponding to all index tuples which does not match the LastName condition.

Index Condition pushdown is enabled by default; It can be controlled with the Optimizer_switch system variable by setting the Index_condition_pushdownflag. 8.9.2, "Controlling switchable optimizations".