Handling ORDER BY When Index Order Differs and Using Descending Indexes in MySQL

When a query requires sorting (ORDER BY) it is common to add an index on the sorted columns to avoid an explicit sort operation. However, if the physical storage order of the index does not match the requested ORDER BY direction, MySQL may still need to perform a filesort.

The article explains how MySQL executes ORDER BY using an index, illustrated with a simple table that has a primary key id and a composite index ab . For a single‑field descending sort on a , MySQL can traverse the index from the rightmost leaf to the left, fetching the primary key for each row, thus avoiding any sorting step. The execution flow is described step‑by‑step and verified with EXPLAIN output that shows no Using filesort in the Extra column.

For composite sorting, the article shows that when the ORDER BY clause matches the index order (e.g., ORDER BY a DESC, b DESC or ORDER BY a ASC, b ASC ), MySQL again performs a simple left‑to‑right or right‑to‑left index scan without sorting.

When the required order cannot be satisfied by a single directional scan—such as ORDER BY a ASC, b DESC —MySQL must use a filesort, which is demonstrated with an EXPLAIN showing Using filesort . The article introduces MySQL 8.0’s Descending Indexes feature, which allows defining an index like INDEX ab (a, b DESC) . This makes the index store b in descending order for each identical a , enabling the same single‑direction scan and eliminating the filesort.

For environments that run MySQL 5.7 or earlier (which lack descending indexes), the author proposes an application‑side optimization: retrieve rows ordered by a ASC, b ASC (which the index can satisfy) and then reverse the order of rows with the same a value using a stack data structure in the application layer. This approach avoids the database’s sort buffer at the cost of modest application memory.

The article concludes with a summary: InnoDB index trees can be traversed from any leaf; if the ORDER BY direction aligns with the index’s natural order, sorting is unnecessary. Descending indexes (MySQL 8.0+) extend this capability, and when unavailable, application‑level techniques can achieve the same result.