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.

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I've also worked at Percona and InPowered.

  • MrSQL

    I am using MySql 5.6.13 and it seems implicit sorting is not deprecated in this version. Maybe there is a database option to set explicitly? I can't believe they change it without giving the possibility to let old SQL-Statements work correctly.

    • http://www.tocker.ca/ Morgan Tocker

      Deprecated means that it still works, but we are asking you to stop relying on this behaviour.

      Once the behaviour is changed (has not happened yet) It may be possible to introduce a configuration option to behave as it does currently. This is a reasonable feature request, although in most cases the workaround is very easy as well (add an ORDER BY).

  • Mark Callaghan

    Will we soon get fast group by via hash aggregation that doesn't order results?

    • http://www.tocker.ca/ Morgan Tocker

      I hope so :)