Fun MySQL fact of the day: index condition pushdowns

It feels like we've been discussing Using where quite a lot lately and you might be wondering, "there must be a better way". There is. In MySQL 5.6 and beyond, queries may leverage the Index Condition Pushdown Optimisation.

Index Condition Pushdown optimisation allows storage engines like InnoDB to perform its own filtering for queries that do not (or cannot) use covering index optimisation. Let's consider an example secondary index, (a,b,c), and a query like SELECT a, b, c, d FROM my_table WHERE a = 1 AND c = 1:

• We know this query cannot leverage covering index optimisation since d must be retrieved from the PRIMARY index.
• We also know that InnoDB will return all rows matching a=1 to MySQL for supplemental filtering (i.e. Using where). Remember, InnoDB uses prefix matching for look-ups, so it cannot evaluate c=1 without looking at all records where a=1.

This is where Index Condition Pushdowns come in: before InnoDB looks-up the record in PRIMARY, InnoDB may evaluate if c=1 for each record. If it does, then InnoDB will look-up the data record in PRIMARY and return it. If not, InnoDB may continue looking for matching records until it finds one (or not). This sounds like a good thing, and I agree: it eliminates unnecessary reads on the PRIMARY index. Still, Index Condition Pushdowns is not a silver bullet: if we consider reads-per-row, Index Condition Pushdown optimisation requires more reads-per-row than covering index optimisation but is more efficient than MySQL having to do a tertiary read to filter it out when Using where; Using index. So, when you see Using index condition in the Extra column of your EXPLAIN plan, you'll know what you're dealing with and can make the decision whether or not you need to optimise further.