Lecture
This article gives examples of using the ORDER BY clause to sort records in MySQL.
ASC – (default, optional). Sorts the set in ascending or alphabetical order.
DESC – sorts in descending order.
It is also possible to sort in MySQL by several columns, listing them separated by commas in order of priority.
Using the FIELD() function, you can retrieve records from the database in a specific sequence, for example, to display records with certain `id` values first.
You can also combine sorting across several fields to show priority records first, followed by alphabetical order.
Or push unwanted records to the end:
The following example helps when, in an order list, you need to sort price values in ascending order while keeping zero values at the end.
For a table of brands and products, you need to sort brands by the number of products.
A problem arises if dates are stored as text (for example, 23.09.2020). If you sort the table by such a field, the months end up jumbled in the result.
The text date needs to be converted to the datetime type using the STR_TO_DATE() function, specifying the required format:
If the field being sorted contains both text and numbers at the same time, and you only need to sort by the numbers, the data must be cast to a numeric type:
or
for this abstract table we want to find out how the account balance changed over time, using the following:
MySQL may use an index to satisfy the ORDER BY clause, the filesort operation used when an index cannot be used, and execution plan information available from the optimizer for ORDER BY.
ORDER BY statements with and without LIMIT can return rows in a different order.
Using indexes to satisfy ORDER BY
Using filesort to satisfy ORDER BY
Impact on ORDER BY optimization
ORDER BY execution plan information available
In some cases, MySQL can use an index to satisfy the ORDER BY clause and avoid the extra sorting involved in performing a filesort operation.
An index can also be used even when the ORDER BY does not match the index exactly, provided that all the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause. If the index does not contain all the columns accessed by the query, the index is used only if accessing the index is cheaper than other access methods.
Assuming there is an index on (, the following queries may be able to use the index to resolve the key_part1, key_part2)ORDER BY part. Whether the optimizer actually does so depends on whether reading the index is more efficient than a table scan, if columns outside the index also need to be read.
In this query, the index on ( lets the optimizer avoid sorting:key_part1, key_part2)
However, the query uses SELECT *, which may select more columns than key_part1 and key_part2. In that case, scanning the entire index and looking up table rows to find columns not in the index may be more costly than scanning the table and sorting the results. If so, the optimizer probably does not use the index. If SELECT * selects only index columns, the index is used without sorting.
If t1 is an InnoDB table, the table's primary key is implicitly part of the index, and the index can be used to resolve ORDER BY for this query:
In this query, key_part1 is constant, so all rows accessed via the index are in key_part2 order, and the index on ( lets sorting be avoided if the key_part1, key_part2)WHERE clause is selective enough to make an index range scan cheaper than a table scan:
In the following two queries, whether the index is used is similar to the same queries with DESC not shown previously:
Two columns in an ORDER BY clause can sort in the same direction (both ASC or both DESC) or in opposite directions (one ASC, one DESC). The condition for using the index is that the index must have the same homogeneity, but not necessarily the same actual direction.
If a query mixes ASC and DESC, the optimizer may use an index for the columns if the index also uses the corresponding mixed ascending and descending column types:
The optimizer may use an index on ( key_part1, key_part2) if key_part1 is descending and key_part2 is ascending. It can also use the index for these columns (with a reverse scan) if key_part1 is ascending and key_part2 is descending. («Descending indexes»).
In the following two queries, key_part1 is compared against a constant. The index is used if the WHERE clause is selective enough to make an index range scan cheaper than a table scan:
In the following query, key_part1 is not named in ORDER BY, but all selected rows have a constant key_part1 value, so the index can still be used:
In some cases MySQL cannot use indexes to resolve ORDER BY, although it may still use indexes to find rows matching the WHERE clause. Examples:
The query uses ORDER BY on different indexes:
The query uses ORDER BY on non-consecutive parts of an index:
The index used to fetch the rows differs from the one used in ORDER BY:
The query uses an ORDER BY expression that includes terms other than an index column name:
The query joins many tables, and ORDER BY does not use all the columns of the first non-constant table used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)
The query has different ORDER BY and GROUP BY expressions.
An index exists only for a prefix of the column named in the ORDER BY clause. In this case, the index cannot be used to fully determine the sort order. For example, if only the first 10 bytes of a CHAR(20) column are indexed, the index cannot distinguish values beyond the 10th byte, and a filesort is required.
The index does not store rows in order. For example, this is true for a HASH index on a MEMORY table.
Index availability for sorting can depend on the use of column aliases. Suppose column t1.a is indexed. In this statement, the column name in the select list is a. It refers to t1.a, as does the a reference in ORDER BY, so the index on t1.a can be used:
This statement also uses the column name a in the select list, but it is an alias. It refers to ABS(a), as does the a reference in ORDER BY, so the index on t1.a cannot be used:
In the following statement, ORDER BY refers to a name that is not a column name in the select list. But table t1 has a column named a, so the ORDER BY reference is treated as t1.a, and the index on t1.a can be used. (The resulting sort order may of course be completely different from the sort order of ABS(a).)
Previously (MySQL 5.7 and earlier), GROUP BY sorting was performed implicitly under certain conditions. In MySQL 8.0 this no longer happens, so it is no longer necessary to add ORDER BY NULL at the end to suppress the implicit sort (as was done previously). However, query results may differ from those of earlier MySQL versions. To produce a given sort order, specify an ORDER BY clause.
If an index cannot be used to satisfy an ORDER BY clause, MySQL performs a filesort operation that reads the table rows and sorts them. A filesort represents an additional sorting phase during query execution.
To obtain memory for filesort operations, starting with MySQL 8.0.12, the optimizer allocates memory buffers incrementally as needed, up to the size specified by the sort_buffer_size system variable, instead of allocating a fixed sort_buffer_size number of bytes up front, as was done prior to MySQL 8.0.12. This lets users set sort_buffer_size to higher values to speed up larger sorts, without worrying about excessive memory usage for small sorts. (This benefit may not apply to multiple concurrent sorts on Windows, which has weak malloc multithreading.)
A filesort operation uses temporary files on disk as needed if the result set is too large to fit in memory. Some kinds of queries are particularly well suited to filesort operations performed entirely in memory. For example, the optimizer can use filesort to process ORDER BY efficiently in memory, without temporary files, 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 that do not use filesort, try lowering the max_length_for_sort_data system variable to a value suitable for triggering filesort. (A sign that this variable is set too high is a combination of high disk activity and low CPU activity.) This technique applies only up to MySQL 8.0.20. Starting with version 8.0.20, max_length_for_sort_data is deprecated due to optimizer changes that made it obsolete and no longer have any effect.
To increase ORDER BY speed, check whether you can get MySQL to use indexes rather than an extra sorting phase. If that 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 writing to disk and doing merge passes).
Note that the size of the column values stored in the sort buffer is affected by the value of the max_sort_length system variable. For example, if the tuples store values of long string columns and you increase max_sort_length, the size of the sort buffer tuples also increases, and you may need to increase sort_buffer_size as well.
To track the number of merge passes (for merging temporary files), check the Sort_merge_passes status variable.
Increase the read_rnd_buffer_size variable value so that more rows are read at once.
Change the tmpdir system variable to point to a dedicated file system with plenty of free space. The variable value can specify multiple paths, which are used in round-robin fashion; you can use this feature to spread the load across several directories. Separate paths with a colon (:) on Unix and a semicolon (;) on Windows. The paths should point to directories on file systems located on different physical disks, not on different partitions of the same disk.
Using EXPLAIN, you can check whether MySQL can use indexes to resolve the ORDER BY clause:
If the Extra column of the EXPLAIN output does not contain Using filesort, an index is used and filesort is not performed.
If the Extra column of the EXPLAIN output contains Using filesort, no index is used and filesort is performed.
Additionally, if a filesort is performed, the optimizer trace output includes a filesort_summary block. For example:
peak_memory_used indicates the maximum amount of memory used at any point during the sort. This value need not equal the sort_buffer_size system variable. Prior to MySQL 8.0.12, the value of sort_buffer_size is shown in the output instead. (Prior to MySQL 8.0.12, the optimizer always allocated sort_buffer_size bytes for the sort buffer. Starting with version 8.0.12, the optimizer allocates sort buffer memory incrementally, starting with a small amount and adding more as needed, up to sort_buffer_size bytes.)
The sort_mode value contains information about the contents of the tuples in the sort buffer:
This indicates that the sort buffer tuples are pairs containing the sort key value and the row ID of the original table row. The tuples are sorted by sort key value, and the row ID is used to read the row from the table.
This indicates that the sort buffer tuples contain the sort key value and the columns referenced by the query. The tuples are sorted by sort key value, and the column values are read directly from the tuple.
As in the previous case, but the extra columns are packed together tightly instead of using fixed-length encoding.
EXPLAIN does not distinguish whether the optimizer performs the filesort in memory or not. Whether filesort uses in-memory processing can be seen in the optimizer trace output. Look for filesort_priority_queue_optimization.
Comments