Descending Indexes

Any developer will create many indexes for a database to speed-up queries, but very seldom the column order direction within the index is considered. For a single-column index the ordering will not make any difference as the index can be scanned by the database forwards and backwards. But for multi-column indexes, matching ordering of the index and the query is crucial to get good performance.

Usage

MySQL / PostgreSQL

SELECT * FROM highscores ORDER BY score DESC, created_at ASC LIMIT 10;

-- Index will not be used for sorting:
CREATE INDEX highscores_wrong ON highscores (score, created_at);

-- Index will be used for sorting:
CREATE INDEX highscores_correct ON highscores (score DESC, created_at ASC);

Detailed Explanation

By default, a database will sort the columns in an index in ascending order if no ordering is specified. The indexes ordering will not make any difference for a single-column index, as the database is intelligent enough to scan the index in forwards and backwards order as needed. But for multi-column indexes using mixed-ordering like ASC and DESC ordering, the index will not be used to get a sorted result. The structure of a B⁺-tree does not support getting sorted results for opposed sorting directions. Any row matching the index will be used and needs to be sorted at the last step. For larger temporary results, all rows may need to be written to disk as the allowed memory for a query is not large enough. The query will pay a huge performance penalty whenever this is happening, as every simple read request to the database is now also writing to disk. To skip the extra sorting step, the index should be created with the same sorting which will be used by the query.

Additional Resources

• MySQL Documentation: Descending Indexes in the optimization and indexes chapter.
• PostgreSQL documentation: Indexes and ORDER BY in the indexes and it's options chapter.
• Percona Blog: Descending indexes in MySQL 8.0 and how to workaround them in MySQL 5.7.

Tobias Petry

SQL for Devs Founder