Mastering MySQL ORDER BY: Full-Field vs RowID Sorting and Optimizations

Assume a user table with columns id (PK), username, age, and city. The requirement is to retrieve usernames from city "南京", sorted by username, and return the first 1000 rows.

Full‑field sorting

MySQL creates a sort_buffer that stores all selected fields (city, username, age). The execution flow is:

Initialize sort_buffer with the three fields.

Use the city index to find the first matching primary‑key id.

Fetch the full row (back‑table lookup) and store the three fields in sort_buffer.

Repeat steps 2‑3 for each matching row.

When all rows are collected, sort the buffer by username.

If the data size exceeds sort_buffer_size, MySQL writes temporary files to disk and performs an external merge sort.

Return the first 1000 sorted rows.

The Using filesort flag in EXPLAIN indicates this process.

RowID sorting

When a row’s length exceeds the value of max_length_for_sort_data, MySQL switches to rowid sorting, storing only the sorting column and the primary key in the buffer. show variables like 'max_length_for_sort_data'; Default is 1024. It can be changed: SET max_length_for_sort_data = 16; With the example table (total length 36), setting the variable to 16 forces rowid sorting. The new flow stores only username and id in the buffer, requiring an additional back‑table lookup after sorting.

ORDER BY optimization with indexes

If a composite index on (city, username) exists, MySQL can retrieve rows already ordered by username, eliminating the need for a sort buffer.

alter table user add index idx_city_username(city, username);

The execution then becomes:

Locate the first matching city='南京' entry in the composite index.

Fetch the row (back‑table lookup) and return username, city, age.

Continue scanning the index until 1000 rows are returned.

Because the index is ordered, EXPLAIN no longer shows Using filesort, and only 1000 index entries need to be read.

Further performance gain is possible with a covering index that includes all selected columns:

alter table user add index idx_city_username_age(city, username, age);

This eliminates the back‑table lookup entirely.

Choosing the right index balances query speed against index maintenance overhead.