I happened on a query where, by mistake, an
SELECT ... ORDER BY x DESC LIMIT 1
was written as
SELECT ... GROUP BY x DESC LIMIT 1
And it took me by surprise to realize GROUP BY x DESC is a valid statement. I looked it up: yep! It’s documented.
In MySQL, GROUP BY results are sorted according to the group statement. You can override this by adding ORDER BY NULL (see past post). I wasn’t aware you can actually control the sort order.
And how is it different form having an ORDER BY clause as well?
MySQL can merge ORDER and GROUP clauses, so as long as your statement is optimized OK, I would recommend you use an explicit ORDER BY and stay within the borders of the standard SQL.
MySQL has a lot of extensions to the grammar, which, if you look a bit deeper, are more resemblant with bugs than with features.
Yes.
It seems like such a nice syntax…until you realize that the commitment to ordering the result according to the GROUP BY clause actually takes away other opportunities to optimize the statement. Adding ORDER BY NULL to prevent that, is not something any sane query tool will figure out.
Anyway, perhaps it’s not too late to have it fixed. Please add your comments here if you agree:
http://bugs.mysql.com/bug.php?id=30477
@Roland – I read your comment and the bug and my first thought was that MySQL didn’t commit themselves to ordering the GROUP BY result. Then I read the docs and realized that they did. Too bad.
Oracle made a few users unhappy when hash-based group by algorithms were used.
@Mark – yes, it is all very unfortunate.
Also see:
http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/
Personally, I would love to see this “feature” removed. I wouldn’t mind if backward compatibility would be broken for that, but I understand why some people would be reluctant to do that. Anyway, I think the proposal I did in #30477 is a pretty reasonable, considering all circumstances.