GROUP BY needs ORDER BY NULL to avoid filesort

I've noticed again and again that when I EXPLAIN queries with a GROUP BY clause, I get filesort as an extra condition. A long time ago I read a suggestion to use GROUP BY NULL in these cases to avoid the filesort, and it does indeed eliminate that unsavoury looking filesort condition.

I would think that if no ORDER BY clause is present that the dbms would just present an arbitrary order or whatever is most efficient rather than ordering by some mysterious column which requires a filesort. It seems strange to me that I need to include an extra direction which basically amounts to saying "don't do anything stupid".

My question is why is this even necessary and is adding ORDER BY NULL actually helping performance?

MySQL 5.7 Reference Manual / ... / SELECT Syntax


If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL

StackExchange Database Administrators Q#208166, answer score: 13