Skip to content
3.16
Sorting with indexes
3.18

Efficiently ordering results in MySQL

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.

Basic order by operation

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 |

Indexes and ordering

To avoid the costly sorting operation, we need to add an index to the birthday column of the people table.

SQL
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.

Deterministic sorting

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.

Backward index scans

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.

Conclusion

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.

About this lesson

Sorting your queries is not free, but we can make it a lot cheaper with indexes. Rule number one is always don't sort your rows if you don't need them sorted though!

08:42
Closed captioned

Meet your instructor, Aaron Francis

Developer Education

I’m a dad to one-year-old twins and a software developer with a keen interest in processes and automation.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.