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
dmust be retrieved from the
- We also know that InnoDB will return all rows matching
a=1to MySQL for supplemental filtering (i.e.
Using where). Remember, InnoDB uses prefix matching for look-ups, so it cannot evaluate
c=1without looking at all records where
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.