Fun MySQL fact of the day: a free ORDER

2019-04-15 23:06UTC

On February 28, we discussed that the order of results from a query in the absence of an ORDER BY clause are implementation specific. We also observed that in InnoDB, this implementation-specific behaviour tends to be the order of records as they are sorted in the index from which they are queried (I feel it's worth repeating again today that this is not by specification and purely by coincidence). Today, we'll ponder this fun fact and combine it with some of the other facts we'd discussed over the last month.

To do this, let's imagine we have a secondary index, (a,b,c). Let's also imagine a query, SELECT a, b, c FROM my_table WHERE a = 1 ORDER BY a. We know a few things:

As such, the ORDER BY in this scenario is a complete no-op: the data is already ordered by a when read from the index and neither MySQL nor InnoDB need to do any additional work. Not too bad, huh?