Fun MySQL fact of the day: uneliminating the eliminated
Yesterday, we looked at an ORDER BY
leveraging index optimisation, which essentially amounted to no sorting work whatsoever. But what would happen if we changed the query just a tiny bit? We'd get a gruesomely fun fact.
Considering the same secondary index as yesterday, (a,b,c)
, what would happen if we changed yesterday's query to SELECT a, b, c FROM my_table WHERE a = 1 ORDER BY a, b
? The difference here is just that we're explicitly ordering by b
, even though we already know it's unnecessary to add. For the same reasons as yesterday, we know this query can still leverage covering index optimisation (Using index
). But if you run the query through EXPLAIN
, you'll notice the Extra
specifies Using where; Using index
. This is interesting if not completely unexpected. As it turns out, it's a long-standing bug fixed in MySQL 8.0.13 that involves some stuff we know and some stuff we haven't discussed yet.
Going back to last Thursday's Fun MySQL Fact Of The Day, the MySQL optimiser will try to eliminate unnecessary conditions. In this case, as yesterday, MySQL will eliminate the supplemental filtering of the condition a=1
, since MySQL knows that InnoDB will already do it. For similar reasons, MySQL will also attempt to eliminate unnecessary ORDER BY
entries based on the conditions in the WHERE
clause.
And here is where the fun starts: because we have WHERE a=1
and we're using a covering index, MySQL knows it can eliminates a
from the order list. But, it doesn't eliminate b
, since b
isn't in the WHERE
clause. At this point of the optimisation chain, MySQL identifies that the query does specify result ordering because of the b
remaining in the order list. Dutifully, MySQL will plan out the ordering strategy, starting with uneliminating the previously-eliminated condition, a=1
. Can you guess what happens next?
Well, MySQL doesn't reeliminate the uneliminated a=1
and the query is now subject to additional filtering Using where
. Whoops.