Descending Indexes and Sort Elimination

Utilize descending indexes to optimize reverse order scans without post-fetch sorting.

In this lesson, you will learn how to leverage descending indexes to satisfy ORDER BY DESC queries instantly by eliminating the need for a costly post-fetch sort operation.

The Cost of Sorting

When you write a query like SELECT * FROM Orders ORDER BY OrderDate DESC, the database engine must first locate the relevant rows and then perform an internal sort to arrange them from newest to oldest. If you have a massive table, this "sorting" phase consumes significant CPU and memory.

In a standard B-Tree index, data is stored in ascending order. If you request descending order, the database must perform a "backward scan." While it can traverse the leaf nodes from right to left, it often still needs to perform a secondary operation to satisfy complex sort requirements.

How Descending Indexes Help

A descending index is a variation of the B-Tree index where the leaf nodes are explicitly stored in descending order. When you create an index using the DESC keyword, you are essentially telling the database, "I want this data pre-sorted in reverse."

Consider the following index creation:

CREATE INDEX idx_orders_date_desc ON Orders (OrderDate DESC);

Once this index exists, a query requesting the latest orders becomes trivial:

SELECT * FROM Orders 
ORDER BY OrderDate DESC 
LIMIT 10;

Because the index itself is already physically ordered from highest to lowest, the database engine can perform a simple "Index Scan" and pluck the first 10 entries directly. There is no "Sort" operator in the execution plan because the data is retrieved exactly in the requested order. This is known as Sort Elimination.

Important Considerations

Direction Matters: An index created as (ColumnA ASC, ColumnB DESC) cannot be used for a query that sorts both columns as ASC. The directions must match your query.
Maintenance: Remember that adding indexes adds overhead to write operations (INSERT, UPDATE, DELETE). Only create descending indexes if your workload involves frequent, high-performance descending sorts that justify the extra maintenance cost.

Key Takeaway: By using a descending index, you store data in the sequence your queries require, allowing the database to skip the expensive sorting process entirely.