Usually my queries are using GROUP BY either by DATE or DATETIME column. So far in MySQL 5.7 the result set was always chronologically sorted. But recently after upgrading to MySQL 8 by accident I found a bizzare result set where DATETIME was just partly sorted.
where time>='2023-08-01' and time<'2023-08-10'
The result set started with all the hours of 2023-08-02 and the hours of 2023-08-01 were placed after 2023-08-09. This situation happens rarely, but I cannot rely that it will not happen as this unsorted result set distorts the charts, which are build upon it.
According to following blog:
In MySQL, historically GROUP BY was used to provide sorting as well. If a query specified GROUP BY, the result was sorted as if ORDER BY was present in the query. [...] This has changed in 8.0 as it no longer supports either implicit or explicit sorting for GROUP BY.
And according to MySQL documentation
Previously (MySQL 5.7 and lower), GROUP BY sorted implicitly under certain conditions. In MySQL 8.0, that no longer occurs, so specifying ORDER BY NULL at the end to suppress implicit sorting (as was done previously) is no longer necessary. However, query results may differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.
My question is should I from now on in the case of using MySQL 8 always put ORDER BY after GROUP BY, so that to have chronologically sorted results in 100% of the cases? Should the query end to look like following?
GROUP BY TimeColumn
ORDER BY TimeColumn;
In the case of only GROUP BY the EXPLAIN command is not showing Using filesort, while when there is also ORDER BY clause the query optimizer is Using filesort. For comparison in MySQL 5.7 when ordering is done when grouping there is no Using filesort.
Using filesort is possibly making the excution of queries harder, respectively slower, so is MySQL 8 less effective than MySQL 5.7 when the idea is to get the same chronologically ordered and grouped by DATE or DATETIME column result set?
Should I increase the value of sort_buffer_size from the default 0.25MB?
I am not posting table structures or other more detailed information, because my question is more general.
I am using MyISAM engine and started considering moving to MariaDB, because MyISAM is quite abandoned in the newer MySQL versions. According to MariaDB documentation:
By default, if a GROUP BY clause is present, the rows in the output will be sorted by the expressions used in the GROUP BY.
This means that the problem I am facing with MySQL 8 and thus posting my question here is not relevant to MariaDB.
My question is should I from now on in the case of using MySQL 8 always put ORDER BY
Yes. If you need the rows in a specific ordering, always use ORDER BY.
Otherwise you may get lucky and get the rows in one ordering, but later on if you upgrade the database or change the data in any way, "the query will suddenly start to fail". Add ORDER BY and sleep well at night.
Again, in the absence of an ORDER BY clause, the engine is free to return the rows in any ordering, and that ordering can change at any time without notice.