Understanding MySQL 8.0 Descending Indexes and Their Performance Impact

MySQL historically stored indexes only in ascending order, ignoring any DESC specification, which could lead to sub‑optimal performance for queries that order results descendingly.

Two example queries are shown: one ordering by id DESC and another mixing ascending and descending columns, both suffering from high OS resource consumption and the Using temporary; Using filesort extra in their execution plans.

Execution plans for a table t1 with a column rank1 are compared: the ascending index plan and the descending index plan, where the latter adds the temporary table and filesort steps, indicating significant performance overhead.

MySQL 8.0 introduces true descending (reverse‑ordered) indexes, eliminating the need for filesort in many cases. After importing data into MySQL 8.0 and converting the original index to a descending one, the second query’s execution plan no longer shows the temporary table or filesort, confirming the performance benefit.

Composite descending indexes can also be created, allowing various combinations of columns (e.g., (rank1 ASC, rank2 ASC) , (rank1 DESC, rank2 DESC) , etc.) to support different query patterns.

Sample queries demonstrating the use of these indexes include simple selections ordered by a single column and more complex selections ordering by multiple columns, illustrating how the new descending indexes can be leveraged in practice.