Why would anyone ever disable `index_condition_pushdown` (ICP)? I'm just wondering if there is every a valid reason to disable index_condition_pushdown?


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


So essentially if I have a table, people(zipcode,lastname,address) with all columns on the index this permits what other databases call Index-only scans, a query

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


can be satisfied without visiting the table.

How is it advantageous to visit the table in addition, or to not use the index at all -- the only two options I see that can result from disabling  index_condition_pushdown?
There are two related things...

Covering index:  When the index is "covering" because all columns mentioned anywhere in the SELECT are contained in the index, the query has always been performed via "index-only scan".  MySQL's EXPLAIN indicates it with

   Using index


Think of it as treating the Index BTree as a 'table'.

Index Condition Pushdown:  The relatively new (5.6) 'ICP' applies to other situations -- when the part of the index being used for filtering is not sufficient.

Version 4.0 split the "Handler" from the "Storage Engines".  The "Handler" does parsing, optimization, etc, etc.  The "Engine" talks to the disk.  This implies that rows are often fetched from disk by the engine, then handed off to the handler for more processing.  In some situations, this handoff can be avoided -- by pushing more processing down from the handler into the engine.  ICP is such a case.  In the past, stuff that was not in the index, but tested in the WHERE, was handled only by the Handler.  Now, the engine can handle it.  EXPLAIN indicates it via

   Using index condition


With ICP, InnoDB tries to read the row and make the rest of the WHERE tests.  This (usually) avoids handing off rows that are to be filtered out.

I have not heard of a case to turn ICP off.  I suspect most 'modes' exist "just in case" the Optimizer has a surprise bug for which the best solution is to turn off a feature.

Your query

Are there only 3 columns in the table?  If so, then a "table scan" is identical to an "index scan"; either would suffice.  Given a choice, the optimizer would pick an index starting with zipcode.  But it would not use the other 2 columns in the index for filtering -- because of the leading wildcard.

If there are only 3 columns in the table then a 3-column index would be "covering".

If there are more then 3 columns in the table, ICP lets all the filtering is done in the storage engine.  Without ICP, only the filtering by zipcode is done in the storage engine; the rest would be handed off to the Handler.