Heads up – Implicit sorting by GROUP BY is deprecated in MySQL 5.6

For those who were unaware, in MySQL the following statements are currently identical:

SELECT MAX(Population), CountryName FROM City GROUP BY CountryName;
SELECT MAX(Population), CountryName FROM City GROUP BY CountryName ORDER BY CountryName;

That is to say that regardless of whether or not you asked for it, whenever you chose to GROUP BY, you will also have data sorted and returned in that order too.

The problem with this, is that it can result in worse performing queries. Sorting either reduces the number of execution plans possible, or requires an additional step to sort the data. Which is why many DBAs advocate writing group by queries with ORDER BY NULL. i.e.

SELECT MAX(Population), CountryName FROM City GROUP BY CountryName;

Should be written as:

SELECT MAX(Population), CountryName FROM City GROUP BY CountryName ORDER BY NULL;

But, as Roland Bouman notes in BUG #30477 there is no standard which requires MySQL to order data in this way, and this behaviour is not present in other databases. So in MySQL 5.6, it was decided that relying on this implicit sorting should be deprecated. From
the manual:

“Relying on implicit GROUP BY sorting in MySQL 5.6 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause. GROUP BY sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.”

So nothing has changed yet, but please make sure you are using GROUP BY CountryName ORDER BY CountryName if your application requires it.