0

I would like to ask if MySQL syntax GROUP BY ... ASC/DESC was removed or not? By this worklog task it should be removed but it seems that it is not truth. There are no details in which version it was applied first time.

By this db-fiddle demo it still works well on any MySQL version (but on 5.7 and 8.0 sql_mode ONLY_FULL_GROUP_BY must be disabled or ANY_VALUE() must be used to avoid error ER_WRONG_FIELD_WITH_GROUP). I know that aim in this demo can be re-written using inner join and subquery but in my use case it is 5 times slower on table with 4.500.000 rows. Demo is simplified. My real use case is much similar to this fiddle.

MySQL doc says that results from GROUP BY, used that way I used, are non-deterministic and I should not rely on it. But in my case result are always identical on any MySQL version (can be tested be switching version in db-fiddle). There ore only two requirements: engine must be InnoDB and I have to create index for columns used in GROUP BY (both requirements are not problem - InnoDB and index is performance advantage). I'm using this solution because it is much faster then any other solution using inner join, subqueries etc.

However syntax is removed or not there is alternative which produces identical result. So GROUP BY col1 ASC/DESC can be rewriten as GROUP BY col1 ORDER BY col1 ASC/DESC. Which one is better to use in my case? Both are identical what about results and performance too.

Thanks for any advice.

Improve this question
5
  • By this db-fiddle demo it still works well Now it uses 8.0.12. SELECT Syntax: As of MySQL 8.0.13, the GROUP BY extension is no longer supported: ASC or DESC designators for GROUP BY columns are not permitted. The same code on dbfiddle.uk, which uses 8.0.13, produces the error message.
    – Akina
    Commented Apr 11, 2019 at 9:35
  • Thank you @Akina. I used alternative with GROUP BY col1 ORDER BY col1 ASC/DESC and it si working well dbfiddle.uk/… So, now I know that GROUP BY ... ASC/DESC can not be used.
    – mikep
    Commented Apr 12, 2019 at 11:35
  • The Optimizer has 'always' merged the GROUP BY and ORDER BY when possible. So, there is no performance loss.
    – Rick James
    Commented Apr 22, 2019 at 23:17
  • Upon rereading, I see that you are really asking about how to optimize a "groupwise max". That is a tough problem and it got worse with the advent of ONLY_FULL_GROUP_BY. Follow the tag I added; you may find some useful ideas. Are you saying that GROUP BY x DESC was providing an optimization that is no longer available?
    – Rick James
    Commented Apr 22, 2019 at 23:21
  • Hehe, make a simple change in the Fiddle, and both SELECTs give "1st Bob" -- change to KEY ix_asker (asker DESC) ! (I suggest that 3 rows is not enough to test the code.)
    – Rick James
    Commented Apr 22, 2019 at 23:27

1 Answer 1

Reset to default
0

From the "changelogs". This was unusually fast from 'deprecation' to 'removal'!

2018-07-27 8.0.12 General Availability & 2018-07-27 5.7.23 General Availability

Explicit ASC or DESC qualifiers for GROUP BY clauses are now deprecated and will be removed in a future MySQL version.

2018-10-22 8.0.13 General Availability -- Incompatible Change

The deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. Queries that previously relied on GROUP BY sorting may produce results that differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.

Queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses should be amended. Otherwise, upgrading to MySQL 8.0.13 or higher may fail, as may replicating to MySQL 8.0.13 or higher slave servers.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.