In MySQL, factually GROUP BY was used to deliver sorting as well. If a query stated GROUP BY, the result was sorted as if ORDER BY was existing in the query.
MySQL at this point implicitly sorts the outcomes from GROUP BY (i.e. in the nonappearance of ASC or DESC designators for GROUP BY columns ).
MySQL also reinforced explicit sorting with GROUP BY (i.e. by using clear ASC or DESC designators for GROUP BY columns).
This has reformed in 8.0 as it no extended supports either implicit or explicit sorting for GROUP BY. In this blog post, I will clarify why this alteration became essential and also the work done as a pioneer to this modification.
GROUP BY in MySQL
To group a set of rows, MySQL optimizer selects changed methods. One of them is to sort the rows already grouping them. This types it easy to group one group after one more. It also becomes cheap if there is an index that could be hand-me-down to get sorted rows. If there is no index, MySQL optimizer could silent decide to do outside (file sort) sorting before grouping.
As seen in the instance, before accumulation index to the table, MySQL uses external sorting to do GROUP BY. For the instance query I have enforced the plan by using SQL_BIG_RESULT (as MySQL will not select this proposal for the dataset we have). But MySQL would use this proposal to group in the nonappearance of an index to get sorted rows and using temporary table develops costly because of large quantity of groups. Once the index is extra, it resorts to by means of the index to do GROUP BY.
But taking sorted rows before grouping is not a need. Optimizer can choose to make use of a temporary table to do it. Each row in this table would be a grouped row and with each inbound row, the row consistent to that group in the table is restructured. Sorting is not wanted here. However, as GROUP BY in MySQL was predictable to sort, it was required to sort the grouped rows even in this case .
In the sample query, we can understand that though temporary table is used, MySQL silent does external sorting. Users would have to explicitly require ORDER BY NULL to let MYSQL tell that GROUP BY want not to have to sort. So, a non-standard (ORDER BY NULL) syntax was wanted to counter the effect of additional non-standard extra time (GROUP BY sorting). It’s much cleaner now that we have eradicated that dirtiness.
Elimination of implicit sorting for GROUP BY
Some time back I was annoying to fix bug 71804. The writer expected MySQL to not do the needless file-sort it was doing for GROUP BY. Trying to type a patch for the bug made us understand that improving this specific state is not very straight forward since of the support for implicit and explicit sorting that GROUP BY only if. So, we decided that before this optimization could be complete, we should be re-factoring code linked to sorting for GROUP BY.
The primary step in liability that was to eliminate the implicit sorting for GROUP BY. As stated in the user guide here, it was definite to eliminate the support for it some time back . It has been complete as part of the descendant index feature in 8.0.
As seen in the sample above, sorting is not done for the query. As a result, grouped rows are not sorted in the outcome. If users want sorted rows, they must require ORDER BY in the query.
In MySQL 5.7 and lower versions, users find the subsequent warning in the guide.
“GROUP BY implicitly sorts by defaulting (that is, in the nonappearance of ASC or DESC designators for GROUP BY columns). However, depend on implicit GROUP BY sorting (that is, sorting in the nonappearance of ASC or DESC designators) or explicit sorting for GROUP BY (that is, by means of explicit ASC or DESC designators for GROUP BY columns) is deprecated. To yield a given sort order, run an ORDER BY clause.“
Elimination of explicit sorting for GROUP BY
When it emanated to removal of explicit sorting, it was a bit more complicated to do it. We could not remove it unless MySQL maintained ORDER BY with ROLLUP. ROLLUP with ORDER BY was not permitted in MySQL 5.7 and earlier versions. So, as an different, users would use GROUP BY ASC/DESC to get sorted information with ROLLUP (Although the sorting was very limiting with super aggregate rows always located after the rows used to calculate them in case of ASC and vice versa for DESC). We had to lift this restriction before we detached the support for explicit sorting for GROUP BY.
MySQL now permits ORDER BY with ROLLUP. I have described in detail on how to make use of this development here. As clarified in the same blog, if users want the precise same sorting order of NULLs as that of MySQL 5.7 for ROLLUP, they should use the GROUPING() function to re-write the query in a modest way.
So, in short we have done the subsequent things as forerunners to eliminating explicit sorting for GROUP BY.
1. Addition GROUPING() function
2. Elimination of implicit sorting for GROUP BY
3. Permitting ORDER BY with ROLLUP
And finally, we have detached explicit sorting for GROUP BY in MySQL 8.0.13.
We did ask for the communities view some time back. We decided that users who were conscious of this non-standard extension that MySQL providing were fine with it going away.
Conclusion
While we still have some more work to do beforehand we fix bug 71804, we are glad that we got this ended. Satisfy let us know your opinions. Thanks for using MySQL!