How MySQL Executes ORDER BY: Full‑Field vs RowID Sorting Explained

Using the table below as an example, we examine the workflow of the ORDER BY clause.

city column has an index.

Execute:

select city, name, age

from t where city='杭州'

order by name limit 1000 ;

This query finds records where city='杭州' and sorts them by name.

Sorting Process

First, look at the illustration of the city index:

The execution steps are:

Initialize sort_buffer and decide to store the three fields name, city, age.

Find the first primary‑key ID (ID_x) in the city index that satisfies city='杭州'.

Retrieve the full row from the primary‑key index using ID_x, extract name, city, age, and place them into sort_buffer.

Fetch the next primary‑key ID from the city index.

Repeat steps 3 and 4 until the city condition is no longer met.

Quick‑sort the data in sort_buffer by name.

Return the first 1000 sorted rows to the client.

This sorting method is called full‑field sorting because all required fields are placed in sort_buffer for sorting.

Whether the sort happens in memory or uses external sorting depends on the available memory and the sort_buffer_size parameter.

sort_buffer_size defines the memory allocated for sorting; if the data to sort exceeds this size, MySQL resorts to temporary disk files.

If a single row is too large, MySQL changes the algorithm. Assuming the combined length of name, city, and age is 36 bytes, but max_length_for_sort_data = 16, the row is considered too big.

In this case, only name and the primary key id are placed in sort_buffer, so the sorted result lacks city and age, requiring a back‑table lookup.

The revised sorting steps become:

Initialize sort_buffer with two fields: name and id.

Find the first primary‑key ID (ID_x) in the city index that satisfies city='杭州'.

Retrieve the full row using the primary key, place name and id into sort_buffer.

Fetch the next primary‑key ID from the city index.

Repeat steps 3 and 4 until the city condition fails.

Quick‑sort the buffer by name.

Take the top 1000 sorted rows, then use the id values to fetch name, city, and age from the original table.

This is known as rowid sorting .

If memory is sufficient, MySQL prefers full‑field sorting, placing all needed fields in sort_buffer so the result can be returned directly from memory without a back‑table lookup.

Sorting Optimization

Create a composite index on city and name. The query city='杭州' then returns rows already ordered by name, eliminating the need for sorting.

The optimized query process:

Use the (city, name) index to find the first matching primary‑key ID.

Fetch the full row from the primary‑key index and return name, city, age as part of the result set.

Retrieve the next primary‑key ID from the (city, name) index.

Repeat steps 2 and 3 until 1000 rows are collected or the condition fails.

Further improvement is possible with a covering index.

With the (city, name) index, sorting is unnecessary, but age still requires a back‑table lookup.

By creating a covering index (city, name, age), the index itself contains all required columns, eliminating both sorting and back‑table access.

The query then proceeds as:

Use the (city, name, age) index to locate matching rows and return all three fields directly.

Fetch the next indexed record and return it.

Repeat until 1000 rows are retrieved or the condition fails.

Summary

The article covered the sorting workflow, including:

Full‑field sorting.

Rowid sorting.

It also discussed optimization strategies:

Using indexes to make the result set inherently ordered.

Employing covering indexes to return results without additional lookups.

Content compiled from Ding Qi's "MySQL实战45讲".