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.
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);
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
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.
SQL for Devs Founder
Be notified on future content. Never spam.