How MySQL Executes ORDER BY: Index vs Sorting Algorithms Explained

MySQL implements ORDER BY using two approaches.

(1) Retrieve rows in order directly from an ordered index, requiring no additional sorting.

(2) Apply MySQL's sorting algorithm to the result set and then return the sorted rows.

When no suitable index exists, MySQL resorts to sorting.

Current sorting is performed by two algorithms:

(1) Extract the rows that satisfy the filter and the sorting column together with their row pointers, sort them in the sort buffer, then fetch the remaining columns from the table using the pointers.

(2) Extract the sorting column and required other columns in one pass, store the non‑sorted columns in a memory area, sort only the sorting column in the sort buffer, and finally combine the sorted pointers with the stored columns to produce the result set.

The first algorithm has been available since early MySQL versions; the second was introduced in MySQL 4.1. The latter reduces a second table access after sorting, saving I/O at the cost of higher memory usage—a classic space‑for‑time trade‑off.