Optimizing SELECT Statements In this statement, the name of the column in the select list is also  a , but it is the alias name. It refers to ABS(a) , as does the reference to  a  in the  ORDER BY , so the index on  t1.a  cannot be used: In the following statement, the  ORDER BY  refers to a name that is not the name of a column in the select list. But there is a column in  t1  named  a , so the  ORDER BY  refers to  t1.a  and the index on  t1.a  can be used. (The resulting sort order may be completely different from the order for  ABS(a) , of course.) 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. If an index cannot be used to satisfy an  ORDER BY  clause, MySQL performs a  filesort  operation that reads table rows and sorts them. A  filesort  constitutes an extra sorting phase in query execution. To obtain memory for  filesort  operations, as of MySQL 8.0.12, the optimizer allocates memory buffers incrementally as needed, up to the size indicated by the  sort_buffer_size  system variable, rather than allocating a fixed amount of  sort_buffer_size  bytes up front, as was done prior to MySQL 8.0.12. This enables users to set  sort_buffer_size  to larger values to speed up larger sorts, without concern for excessive memory use for small sorts. (This benefit may not occur for multiple concurrent sorts on Windows, which has a weak multithreaded  malloc .) A  filesort  operation uses temporary disk files as necessary if the result set is too large to fit in memory. Some types of queries are particularly suited to completely in-memory  filesort  operations. For example, the optimizer can use  filesort  to efficiently handle in memory, without temporary files, the  ORDER BY operation for queries (and subqueries) of the following form: Such queries are common in web applications that display only a few rows from a larger result set. Examples: For slow  ORDER BY  queries for which  filesort  is not used, try lowering the max_length_for_sort_data  system variable to a value that is appropriate to trigger a  filesort . (A symptom of setting the value of this variable too high is a combination of high disk activity and low CPU activity.) This technique applies only before MySQL 8.0.20. As of 8.0.20,  max_length_for_sort_data is deprecated due to optimizer changes that make it obsolete and of no effect. To increase  ORDER BY  speed, check whether you can get MySQL to use indexes rather than an extra sorting phase. If this is not possible, try the following strategies: •Increase the  sort_buffer_size  variable value. Ideally, the value should be large enough for the entire result set to fit in the sort buffer (to avoid writes to disk and merge passes). SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10; SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15; SELECT ... FROM  single_table  ... ORDER BY  non_index_column  [DESC] LIMIT [ M ,] N ; Influencing ORDER BY Optimization Use of filesort to Satisfy ORDER BY SELECT ABS(a) AS b FROM t1 ORDER BY a; SELECT ABS(a) AS a FROM t1 ORDER BY a; SELECT a FROM t1 ORDER BY a; 1682