Fun MySQL fact of the day: a free
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
care all present in our secondary index,
- InnoDB will return MySQL rows from our secondary index,
(a,b,c), in order of
- The query's
ORDER BYspecifies results in order of
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?