Understanding MySQL ORDER BY: Default vs Rowid Algorithms and Optimization Tips

Default ORDER BY Algorithm

The article starts with a sample table definition and a SELECT statement that uses ORDER BY. The execution plan shows Using filesort, indicating that MySQL must sort the result set after retrieving rows.

CREATE TABLE `waybill` (
  `id` bigint(20) NOT NULL,
  `shipping_number` varchar(26) COMMENT '运单号',
  `order_create_time` datetime COMMENT '开单时间',
  `status` tinyint(1) comment '运单状态',
  PRIMARY KEY (`id`),
  KEY `waybill_index_status` (`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运单表';

explain SELECT shipping_number,age,status FROM `waybill` where status = 4 order by order_create_time desc

The default sorting process involves five steps:

Initialize sort_buffer with the selected columns (shipping_number, order_create_time, status).

Use the index on status to locate matching primary‑key IDs.

Fetch the full rows for those IDs from the primary‑key tree and place them into sort_buffer.

Iterate through all IDs, filling the buffer.

Perform an in‑memory quick sort on order_create_time inside sort_buffer and return the ordered rows.

sort_buffer is a memory buffer used for sorting.

The sort behavior is governed by sort_buffer_size (default 256 KB). If the data to sort exceeds this size, MySQL spills to temporary files, whose count is reported by number_of_tmp_files. Smaller sort_buffer_size leads to more temporary files.

Rowid Sorting Algorithm

The alternative algorithm, called rowid, is selected when a row’s length exceeds the value of max_length_for_sort_data. The parameter can be changed with:

SET max_length_for_sort_data = 16;

max_length_for_sort_data determines which algorithm MySQL chooses; rows longer than this threshold trigger the rowid method.

In the example, the combined length of the three selected columns (26 + 8 + 1 = 35) exceeds 16, so MySQL uses the rowid algorithm.

shipping_number length = 26 order_create_time length = 8 status length = 1

The rowid execution flow mirrors the default one but differs in the data placed into the buffer:

Initialize sort_buffer with order_create_time and id.

Find matching IDs from the status index.

Retrieve order_create_time and id from the primary‑key tree into sort_buffer.

Repeat until all IDs are processed.

Sort the buffer by order_create_time.

Fetch the remaining columns (shipping_number, age, status) from the base table using the sorted IDs and return them.

Differences Between the Two Algorithms

If MySQL worries that the sort memory is insufficient, it switches to the rowid algorithm, which can sort more rows at once but requires extra lookups to the base table.

If enough memory is available, the default algorithm keeps all needed fields in sort_buffer, allowing the result to be returned directly from memory without additional table reads.

The underlying design principle is: use as much memory as possible to minimize disk I/O.

Optimization Strategies

Optimization can be approached in three layers:

First layer: Add indexes on the columns used for sorting to speed up index traversal.

Second layer: Adjust memory‑related settings ( sort_buffer_size, max_length_for_sort_data) to influence which algorithm MySQL selects.

Third layer: Optimize the data model itself, for example by using auto‑increment IDs, Snowflake‑generated IDs, composite indexes, or covering indexes so that the index alone satisfies the query.

Covering indexes store all required columns in the index, eliminating the need to read the base table.