Fun MySQL fact of the day: a free ORDER
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:
- This query is subject to covering index optimisation, since
a,b, andcare all present in our secondary index,(a,b,c). - InnoDB will return MySQL rows from our secondary index,
(a,b,c), in order ofa(specifically wherea=1). - The query's
ORDER BYspecifies results in order ofa.
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?