How MySQL Executes ORDER BY: Full-Field vs RowID Sorting and Index Optimization

When a product manager requests the top 1000 names and ages of people from Shanghai ordered by name, a naive SQL query is written and an index on city is added to avoid a full table scan. Running

EXPLAIN SELECT city, name, age FROM citizen WHERE city = '上海' ORDER BY name LIMIT 1000;

shows Using filesort in the Extra column, meaning MySQL must sort rows after retrieving them.

Full-Field Sorting Process

Initialize sort_buffer with name, city, and age.

Use the city index to locate the first matching primary key id_x.

Fetch the full row from the primary key index and store name, city, age in sort_buffer.

Repeat steps 2‑3, adding each row to sort_buffer until the city condition fails.

Quick‑sort the buffer by name.

Return the first 1000 sorted rows to the client.

This method reads all matching rows (e.g., 4000) and may create many temporary files if sort_buffer_size is insufficient.

RowID Sorting

By setting max_length_for_sort_data = 16, MySQL reduces the data placed in sort_buffer to only name and the primary key id. The sorting algorithm then works on rowid instead of full rows, which reduces memory usage and the number of temporary files, but still requires a final lookup to retrieve city and age from the table.

Using Composite Indexes to Eliminate Sorting

Creating a composite index city,name allows MySQL to satisfy the WHERE city='上海' and the ORDER BY name directly from the index. The execution plan shows Using index condition and no Using filesort. Only the first 1000 matching index entries are read, dramatically reducing I/O.

ALTER TABLE citizen ADD INDEX idx_city_name (city, name);
EXPLAIN SELECT city, name, age FROM citizen WHERE city='上海' ORDER BY name LIMIT 1000;

The plan now reports type=ref, key=city,name, and Extra=Using index condition, confirming that sorting is avoided.

Covering Index for Full Query

Adding age to the composite index ( city,name,age) creates a covering index, so MySQL can return all requested columns directly from the index without touching the base table. The Extra column shows Using index, indicating the most efficient path.

ALTER TABLE citizen ADD INDEX idx_city_name_age (city, name, age);
EXPLAIN SELECT city, name, age FROM citizen WHERE city='上海' ORDER BY name LIMIT 1000;

While covering indexes boost performance, they increase storage and maintenance overhead, so they should be used judiciously based on workload.

References: "How does ORDER BY work?" and related MySQL optimizer trace articles.