Fun MySQL fact of the day:
If, over the last few days, you've started
EXPLAINing your queries more, I commend you: you're on the path to becoming a database query optimiser extraordinaire. You may have also identified that the
Extra column isn't a singleton value. You may have even seen something weird like
Using where; Using index and thought, "well that doesn't make sense". Well, it does. And it doesn't. And it does but still doesn't. It's complicated.
Last Friday, we discussed how MySQL may be tasked with supplemental filtering, and we discussed that you'll know it's happening when you see
Using where in the
Extra column. Then, yesterday, we discussed covering index optimisation, and we considered that you'll know it's happening when you see
Using index. As you've already seen: these don't have to be mutually exclusive. In fact, a query can leverage covering index optimisation but still be subject to additional filtering:
Using where; using index. Why? Well, now we're getting into the bowels of MySQL that even the bug tracker and code comments don't illuminate too brightly.
Let's consider an index,
(a,b) and a query
SELECT a FROM my_table WHERE a > 100. While it is indeed true that InnoDB is, in fact, using a covering index optimisation (i.e. it is not performing a supplemental read from
range type queries are not optimised the same way as
ref type queries. As such, MySQL will indeed use the covering index but still perform additional filtering. You'll also see this same symptoms and behaviour in versions of MySQL before 5.6 for nullable columns in a secondary index, even for
ref query types. This is enough fun for today. We must leave some for tomorrow.