Understanding and Optimizing ORDER BY in MySQL

In everyday development we often use ORDER BY ; this article explores its underlying mechanism, optimization ideas, and common pitfalls.

Using a sample staff table, the query SELECT name, age, city FROM staff WHERE city='深圳' ORDER BY age LIMIT 10; is examined. The execution flow involves initializing a sort_buffer , locating matching primary‑key IDs via the idx_city index, fetching rows, storing the required columns in the buffer, and finally sorting by age before returning the top 10 rows.

Running EXPLAIN shows the use of the idx_city index and the Using filesort flag, indicating that MySQL must sort the result set.

Full‑field sort loads all selected columns into sort_buffer . If the data exceeds sort_buffer_size , MySQL spills to temporary disk files, which can be detected via number_of_tmp_files in information_schema.optimizer_trace .

Rowid sort stores only the sorting column(s) and the primary key in the buffer, reducing memory usage but requiring an extra “back‑table” lookup. The switch between the two methods is controlled by max_length_for_sort_data .

Optimization strategies include:

Creating a composite index (e.g., idx_city_age(city, age) ) so the index itself provides the required order and eliminates Using filesort .

Using a covering index (e.g., idx_city_name_age(city, name, age) ) to avoid any back‑table access.

Adjusting sort_buffer_size and max_length_for_sort_data to favor full‑field sorting when appropriate.

Additional considerations:

If there is no WHERE clause, MySQL may still use an index for ORDER BY … LIMIT m when m is small.

Large LIMIT offsets cause heavy sorting; using “keyset pagination” (remembering the last ID) mitigates this.

When the index order does not match the required ORDER BY direction, MySQL 8.0’s descending indexes can resolve the mismatch.

Multiple values in an IN list may reintroduce sorting because the combined result set is no longer pre‑ordered.

The article concludes with a reminder to like, share, and follow the author’s public account for more technical resources.