Fun MySQL fact of the day: Extra confusing

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 PRIMARY), 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.