Understanding and Optimizing MySQL ORDER BY Execution

Sorting is a common keyword in SQL statements, and improper use of ORDER BY can affect MySQL performance; this article introduces its execution process and optimization methods.

For example, to retrieve the names and ages of the first 1000 people from the city "Hangzhou" sorted by name, the SQL statement is shown (see image of table definition and query). The statement looks straightforward, but its execution needs to be examined.

When a full‑field index on city is used, running EXPLAIN reveals an Using filesort in the Extra column, meaning MySQL will allocate a sort_buffer for sorting. The typical execution flow is illustrated (see images) and can be described as:

Initialize sort_buffer for the fields name , city , age .

Use the city index to locate the first matching primary‑key id (ID_X).

Fetch the full row by primary‑key and store name , city , age in sort_buffer .

Repeat steps 2‑3 for subsequent rows until the city condition fails (reaching ID_Y).

Sort the buffered rows by name .

Return the first 1000 rows to the client.

The sorting may be performed in memory or via external sort, depending on the required memory and the sort_buffer_size setting.

When many columns are returned, the buffer can become large, degrading performance. Adjusting the max_length_for_sort_data parameter (e.g., setting it to 16) limits the row length considered for sorting, so only name and the primary‑key id are placed in the buffer. This changes the execution to a "routId" sort, illustrated with a new step list and accompanying diagram.

Optimization can be achieved by creating a composite index on (city, name) :

alter table t add index_city(city,name);

With this index, EXPLAIN no longer shows Using filesort . The new execution steps are:

Find the first matching primary‑key id via the (city, name) index.

Fetch the full row and return name , city , age directly.

Iterate through the index to retrieve subsequent rows.

Further improvement is possible by extending the index to cover all selected columns:

alter table t add index_city(city,name,age);

Now EXPLAIN shows Using index , indicating a covering index that eliminates the need to read the table rows, resulting in much faster performance.

In summary, while indexes improve read queries, they add overhead on data modification; therefore, index creation should consider the specific usage scenarios.