Why Does MySQL GROUP BY Return Sorted Results? Uncovering Implicit Sorting

Background

When using GROUP BY in MySQL, many developers notice that the result set appears ordered even without an explicit ORDER BY clause. In MySQL 5.7 and earlier the optimizer performed an implicit sort during grouping, a behavior that was later removed.

Implicit Sorting in MySQL 5.7 and Earlier

The MySQL 5.7 Reference Manual states that GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators for GROUP BY columns) . Although this feature is deprecated, it still produced a deterministic order for grouped rows.

Removal in MySQL 8.0

Starting with MySQL 8.0, the optimizer no longer adds implicit sorting for GROUP BY. The 8.0 Reference Manual notes that specifying ORDER BY NULL is no longer necessary, and queries that relied on the hidden ordering may return rows in a different order.

Practical Demonstration

Consider a table T with unordered data:

mysql> SELECT pid, appName FROM T;
+--------+-------------------------+
| pid    | appName                 |
+--------+-------------------------+
| 1      | Dock Sound Redirector   |
| 2      | Blues Music station     |
| 3      | usb tether TRIAL        |
| 4      | Il vero test del QI     |
| 5      | FlightTime Calculator   |
| 6      | ZX Spectrum Emulator    |
| 7      | The City Dress Up       |
+--------+-------------------------+

Experiment 1 – MySQL 5.7.24

SELECT pid, appName FROM T GROUP BY appName;
-- Result is ordered by appName ascending, equivalent to:
SELECT pid, appName FROM T GROUP BY appName ORDER BY appName ASC;

Experiment 2 – MySQL 8.0.16

SELECT pid, appName FROM T GROUP BY appName;
-- Returns rows in the original insertion order (no implicit sort).

SELECT pid, appName FROM T GROUP BY appName DESC;
-- ERROR 1064 (42000): You have an error in your SQL syntax … near 'DESC'

These results show that implicit sorting disappears in MySQL 8.0, and attempting to specify a direction directly after GROUP BY is a syntax error.

Why Implicit Sorting Existed

The optimizer could sort the data before grouping to reduce complexity, especially when the grouping column is indexed. An index provides a natural order, so using it for grouping is cheap and efficient. This design choice led to the “beautiful bug” of hidden ordering.

Index‑Based Grouping

When the grouping column has an index ( appName_idx), MySQL can group without an extra sort:

EXPLAIN SELECT appName FROM 0122_csj_demo GROUP BY appName \G
id: 1
select_type: SIMPLE
type: index
possible_keys: appName_idx
key: appName_idx
Extra: Using index

If no suitable index exists, MySQL falls back to a temporary table with filesort:

EXPLAIN SELECT appName FROM 0122_csj_demo GROUP BY appName \G
type: ALL
Extra: Using temporary; Using filesort

Suppressing Unnecessary Sorting

Developers can explicitly tell MySQL that no ordering is required by adding ORDER BY NULL:

EXPLAIN SELECT appName FROM 0122_csj_demo GROUP BY appName ORDER BY NULL \G
type: ALL
Extra: Using temporary

This forces the optimizer to skip the implicit sort (when it still exists) and avoid the overhead of a temporary table.

Takeaway

When upgrading from MySQL 5.7 (or earlier) to MySQL 8.0, be aware that GROUP BY no longer guarantees ordered results. Add explicit ORDER BY clauses where a specific order matters, and consider using ORDER BY NULL to suppress unwanted sorting in older versions. Understanding this change prevents subtle bugs during migration.