I'm just wondering if there is every a valid reason to disable
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
WHEREcondition for the rows. With ICP enabled, and if parts of the
WHEREcondition can be evaluated by using only columns from the index, the MySQL server pushes this part of the
WHEREcondition 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