ORDER BY NULL in MySQL

mysql, sql-order-by

Solution

It's for performance; adding `ORDER BY NULL` after a `GROUP BY` clause will make your query faster.

An explanation, from the manual:

By default, MySQL sorts all `GROUP BY col1, col2, ...` queries as if you specified `ORDER BY col1, col2, ...` in the query as well. If you include an explicit `ORDER BY` clause that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. If a query includes `GROUP BY` but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying `ORDER BY NULL`. For example:

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

This article describes the author successfully optimising a slow query by exploiting this trick, complete with the relevant parts of the `EXPLAIN` output.

Problem

Original source