Comments on: Discovery of the day: GROUP BY … DESC https://shlomi-noach.github.io/blog/mysql/discovery-of-the-day-group-by-desc Blog by Shlomi Noach Tue, 04 May 2010 17:18:07 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: Roland Bouman https://shlomi-noach.github.io/blog/mysql/discovery-of-the-day-group-by-desc/comment-page-1#comment-13118 Tue, 04 May 2010 17:18:07 +0000 https://shlomi-noach.github.io/blog/?p=2381#comment-13118 @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.

]]> By: Mark Callaghan https://shlomi-noach.github.io/blog/mysql/discovery-of-the-day-group-by-desc/comment-page-1#comment-13096 Tue, 04 May 2010 14:19:38 +0000 https://shlomi-noach.github.io/blog/?p=2381#comment-13096 @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.

]]> By: Roland Bouman https://shlomi-noach.github.io/blog/mysql/discovery-of-the-day-group-by-desc/comment-page-1#comment-13084 Tue, 04 May 2010 12:03:27 +0000 https://shlomi-noach.github.io/blog/?p=2381#comment-13084 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

]]> By: Kostja Osipov https://shlomi-noach.github.io/blog/mysql/discovery-of-the-day-group-by-desc/comment-page-1#comment-13083 Tue, 04 May 2010 11:27:46 +0000 https://shlomi-noach.github.io/blog/?p=2381#comment-13083 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.

]]>