I spent a good amount of time fixing issues that showed up after upgrading a database from MySQL 5.7 to MySQL 8. The upgrade itself went smoothly, but several queries started behaving differently because parts of the application were relying on implicit row ordering without explicitly defining it.
What Changed
MySQL 8 does not introduce a new "ordering rule". The real change is that it removes the accidental stability that older applications sometimes depended on.
The optimizer now has more freedom in how it executes queries, including:
- different join strategies
- hash-based grouping and joins
- different index choices based on statistics
- more parallel execution paths
As a result:
Row order is no longer stable unless it is explicitly defined using
ORDER BY.
Example 1: GROUP BY no longer implies ordering
SELECT DATE(created_at), COUNT(*)
FROM orders
GROUP BY DATE(created_at);
In MySQL 5.7, this often appeared sorted by date because index-driven execution frequently preserved order.
In MySQL 8, grouping may use hash-based execution, which does not preserve order, so the same query can return dates in an arbitrary sequence.
Fix:
SELECT DATE(created_at), COUNT(*)
FROM orders
GROUP BY DATE(created_at)
ORDER BY DATE(created_at);
Example 2: LIMIT with ties is not deterministic
SELECT *
FROM posts
ORDER BY score DESC
LIMIT 10;
When multiple rows share the same score, MySQL is free to return any subset of those tied rows first.
In MySQL 5.7, the result often looked stable due to consistent index usage. In MySQL 8, execution plans can change more easily, so the order among ties can vary between runs.
Fix:
SELECT *
FROM posts
ORDER BY score DESC, id ASC;
Key Lesson
If row order matters, it must be explicitly defined with
ORDER BY. Everything else is an execution detail, not a guarantee.
