Ordering results in a MySQL database is critical for creating effective queries, but doing it efficiently is even more important. In this article, we will explore how to add indexes, use optimized sorting methods, and efficiently execute queries.
We will focus on the people table, which contains 500,000 records, and has no indexes except for the primary key. This is sufficient for this demo, but not ideal for real-world situations.
We will begin by ordering the results of the people table by birthday and using a limit of 10.
SELECT
*
FROM
people
ORDER BY
birthday ASC
LIMIT
10;
When we run this query and examine it using the EXPLAIN statement, we see that it employs the "using file sort" method. This means that MySQL produced a sorted result by sorting all the results, not by reading an index in order.
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| --- | ----------- | ------ | ---- | ------------- | --- | ------- | --- | ------ | -------- | -------------- |
| 1 | SIMPLE | people | ALL | | | | | 491583 | 100.00 | Using filesort |
To avoid the costly sorting operation, we need to add an index to the birthday column of the people table.
ALTER TABLE people
ADD INDEX birthday (birthday);
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| --- | ----------- | ------ | ----- | ------------- | -------- | ------- | --- | ---- | -------- | ----- |
| 1 | SIMPLE | people | index | | birthday | 3 | | 10 | 100.00 | |
This time, it is able to use the birthday index to sort the results, and the "using file sort" method is gone.
In order to ensure that our rows are returned in the same order every time, our sorting must be "deterministic." In many cases, this involves sorting by a unique identifier or primary key.
For example, if we add the ID column to our original query and sort by ID after sorting by birthday, we can ensure deterministic sorting.
SELECT
*
FROM
people
ORDER BY
birthday ASC, ID ASC
LIMIT
10;
In this case, MySQL can still use the birthday index, even though there is another column involved in the sorting process.
In MySQL 8.0 or later, we can perform backward index scans when sorting in descending order.
For example, if we set the birthday index to order in descending order and rerun the query, we can see that MySQL performs a backward index scan. This is a new optimization that MySQL uses when it is able to read an index in reverse order.
Using indexes and optimized sorting methods is critical for efficiently ordering query results in MySQL. By adding indexes to our tables, we can reduce the need for secondary sorting operations and improve query performance.