Index push-down optimization is a new feature in MySQL5.6.

Indexed conditional push down (ICP) is an optimization of MySQL’s use of indexes to retrieve rows from tables. Without ICP, the storage engine traverses the index to find rows in the base table and returns them to the MySQL server for another wave of filtering by the Server layer. When ICP is enabled, if only the columns in the index are used to evaluate some part of the WHERE condition, the MySQL server pushes that part of the WHERE condition to the storage engine. The storage engine then evaluates the pushed index criteria using index entries and reads rows from the table only if the criteria are met.

ICP reduces 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.

The applicability of ICP is limited by the following conditions:

  • ICP is used for range, REF, Eq_REF, and REF_OR_NULL access methods when full table rows need to be accessed.

  • ICP is available for InnoDB and MyISAM tables. (Exception: The partition table in MySQL 5.6 does not support ICP; This issue has been resolved in MySQL 5.7.

  • For InnoDB tables, ICP is only used for secondary indexes. The goal of ICP is to reduce the number of row-wide reads and thus IO operations. For the InnoDB cluster index, the full record is already read into the InnoDB buffer. Using ICP in this case does not reduce IO.

  • Conditions that reference subqueries cannot be pushed down.

  • Unable to push down a condition involving a stored function. The storage engine cannot call the stored function.

  • Trigger condition cannot be pushed down.

To understand how this optimization works, first consider the progress of an index scan when pushed without indexes:

Gets the next row, first reads the index tuple, and then uses the index tuple to find and read the entire table row.

The test applies to part of the WHERE condition for this table. Accept or reject the row based on the test results.

Using the index condition push down, the scan would look like this:

Gets the index tuple of the next row (but not the full table row).

The test applies to part of the WHERE condition for this table and can only be checked using index columns. If the conditions are not met, the index tuple of the next row continues.

If the conditions are met, use index tuples to find and read the entire table row.

The test applies to the remainder of the WHERE condition for this table. Accept or reject the row based on the test results.

EXPLAIN output shows the use of index criteria in the Extra column when pushing with index criteria. It does not show use of indexes, because this is not applicable when full table rows must be read.

Suppose a table contains information about people and their addresses, and the INDEX of the table is defined as INDEX (Zipcode, lastName, firstName). If we know a person’s zip code value but are not sure of their last name, we can search like this:

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

MySQL can use the index to scan people with zipcode=’95054′. The second part (last name LIKE’ % etrunia % ‘) cannot be used to limit the number of rows that must be scanned, so if there is no ICP, this query must retrieve the full table row for all people with Zipcode =’95054’.

With ICP, MySQL checks the last name LIKE’ % etrunia % ‘section before reading the entire table row. This avoids reading the full row that corresponds to the index tuple that matches the Zipcode condition, but does not read the lastName condition.

Index conditional push is enabled by default. You can use the Optimizer_switch system variable to control it by setting the index_condition_PUSHdown flag:

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
Copy the code