Posted over 8 years ago. Visible to the public.

If you GROUP BY, make sure you ORDER BY NULL

TL;DR: If using :group => :some_field you might want to :order => 'NULL'.

According to the man Archive

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 ORDER BY clause explicitly 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

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

This changes the output of EXPLAIN from...

Copy
... Extra, Using where; Using temporary; Using filesort

To...

Copy
... Extra, Using where; Using temporary

Owner of this card:

Avatar
Marcus Mitchell
Last edit:
over 8 years ago
Tags:
mysql, null, optimization
Posted by Marcus Mitchell to HouseTrip Deck
This website uses short-lived cookies to improve usability.
Accept or learn more