Descending Indexes in MySQL 8.0: When and How to Use Them

Your admin dashboard runs a query to show the most recent orders — SELECT id, customer_id, total, created_at FROM orders ORDER BY created_at DESC LIMIT 50 — and despite an index on created_at, EXPLAIN shows Using filesort every time. The index exists, the column is indexed, and yet MySQL 5.7 cannot use it for a descending sort without reading and reversing the entire index traversal. MySQL 8.0 fixed this with true descending indexes, which store B-tree entries in reverse order so the optimizer can walk them forward without any sort step at all. This guide explains exactly when descending indexes eliminate filesorts, how to create them, and how to verify the difference with EXPLAIN.

What Were the Limitations Before MySQL 8.0?

MySQL has accepted the DESC keyword in index definitions since version 3.x — but it was silently ignored. Every index, regardless of how it was declared, was stored in ascending order in the InnoDB B-tree. This meant that when a query needed rows in descending order, MySQL had two options: perform a backward index scan (reading the B-tree leaves right-to-left, which is less cache-friendly) or perform a filesort (reading all matching rows and sorting them in a temporary buffer before returning results).

For single-column descending sorts on low-selectivity ranges, the backward scan was often acceptable. The real problem was composite indexes with mixed sort directions. Consider a query like:

SELECT id, user_id, amount, created_at
FROM transactions
WHERE user_id = 42
ORDER BY created_at DESC;

An index on (user_id ASC, created_at ASC) lets MySQL seek to user_id = 42 and then perform a backward scan on created_at — workable, though not ideal. But the moment your ORDER BY mixes directions — like ORDER BY status ASC, created_at DESC — no existing index can satisfy both sort directions simultaneously in MySQL 5.7. The optimizer is forced into a filesort regardless of what indexes exist.

The Filesort Cost

A filesort materialises all matching rows into a sort buffer (controlled by sort_buffer_size, default 256KB). If the result set exceeds the buffer, MySQL spills to temporary files on disk — meaning disk I/O at query time. For dashboard queries returning thousands of rows, this becomes a steady latency tax that worsens as the table grows and the result set expands.

How Descending Indexes Work in MySQL 8.0

MySQL 8.0 actually honours the DESC keyword in index definitions. When an index column is declared DESC, InnoDB stores the corresponding B-tree entries with their sort key negated — effectively reversing the storage order so that the physically smallest stored key corresponds to the logically largest column value. A forward scan of this B-tree then reads rows from largest to smallest, which is exactly what ORDER BY col DESC requires.

The optimizer tracks the direction of each index column independently. When a query's ORDER BY or GROUP BY clause matches the declared sort order of an index — including mixed ASC/DESC combinations — the optimizer can use a forward index scan with no sort step. The key insight is that forward scans are always more efficient than backward scans because InnoDB's buffer pool and read-ahead mechanisms are optimised for sequential forward page access.

Forward Scan vs Backward Scan

A backward index scan (what MySQL 5.7 used for ORDER BY col DESC on an ascending index) is not free. InnoDB acquires page latches in reverse, read-ahead does not activate for backward traversal, and the page access pattern tends to cause more cache misses at scale. MySQL 8.0's optimizer notes whether a forward or backward scan is needed and reflects this in EXPLAIN via the Backward index scan flag in the Extra column — making it easy to identify queries that would benefit from a descending index.

Creating a Descending Index

Single-Column Descending Index

For a table where nearly all queries fetch the most recent rows first, a descending index on the timestamp column eliminates the backward scan entirely:

-- Table: orders, frequently queried newest-first
CREATE TABLE orders (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id BIGINT UNSIGNED NOT NULL,
    status      VARCHAR(32) NOT NULL,
    total_cents INT UNSIGNED NOT NULL,
    created_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    INDEX idx_created_desc (created_at DESC)   -- true descending index in MySQL 8.0
) ENGINE=InnoDB;

-- Or add to an existing table:
ALTER TABLE orders
    ADD INDEX idx_created_desc (created_at DESC);

Now SELECT ... ORDER BY created_at DESC LIMIT 50 performs a forward scan on idx_created_desc with no filesort. Conversely, a query with ORDER BY created_at ASC on this same index will use a backward scan — so if both sort directions are common, maintain both an ascending and a descending index, or rely on the default ascending index and accept the backward scan for DESC queries.

Composite Descending Index

The more impactful use case is filtering by one column and ordering by another in descending order — the classic dashboard pattern:

-- Composite: equality filter on user_id, then newest-first by created_at
ALTER TABLE orders
    ADD INDEX idx_user_created_desc (customer_id ASC, created_at DESC);

-- This query now uses a forward scan with no filesort:
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 9801
ORDER BY created_at DESC
LIMIT 20;

MySQL seeks to the customer_id = 9801 partition in the index (ascending), then walks forward through the created_at DESC portion — which is stored largest-to-smallest — and returns exactly the 20 most recent rows without ever touching the full table.

Composite Indexes with Mixed Sort Orders

Consider a reporting table that tracks job execution history. The application queries it frequently to show active jobs first, then most-recent within each status:

CREATE TABLE jobs (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    queue       VARCHAR(64) NOT NULL,
    status      TINYINT NOT NULL,   -- 0=pending, 1=running, 2=done, 3=failed
    payload     JSON,
    started_at  DATETIME(3),
    finished_at DATETIME(3),
    created_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),

    -- Mixed sort: ascending status, descending created_at within each status group
    INDEX idx_queue_status_created (queue ASC, status ASC, created_at DESC)
) ENGINE=InnoDB;

-- Query served by a pure forward scan, no filesort:
SELECT id, status, created_at
FROM jobs
WHERE queue = 'email'
ORDER BY status ASC, created_at DESC
LIMIT 100;

In MySQL 5.7, this query would always filesort. In MySQL 8.0 with the index declared exactly as above, EXPLAIN shows no filesort and a row estimate proportional to the actual LIMIT — meaning MySQL stops scanning the index as soon as it has collected 100 rows.

Index Direction Must Match Query Exactly

MySQL 8.0's optimizer requires that the ORDER BY directions match the index column directions either exactly or in complete reversal. An index on (a ASC, b DESC) satisfies:

• ORDER BY a ASC, b DESC — forward scan, no filesort
• ORDER BY a DESC, b ASC — backward scan, no filesort (complete reversal)
• ORDER BY a ASC, b ASC — cannot use this index without filesort
• ORDER BY a DESC, b DESC — cannot use this index without filesort

Design your index direction to match the most frequent query pattern. If you have two dominant patterns with different sort directions, create two separate indexes rather than trying to satisfy both with one.

Verifying with EXPLAIN — Backward vs Forward Scan

EXPLAIN output is the authoritative way to confirm whether your descending index is actually being used and whether a filesort has been eliminated. Here is a complete before/after comparison:

MySQL 5.7 Behaviour (Simulated with Ascending Index)

-- Ascending index only (MySQL 5.7 default, or 8.0 with ASC index)
EXPLAIN SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE customer_id = 9801
ORDER BY created_at DESC
LIMIT 20\G

-- Output:
-- *************************** 1. row ***************************
--            id: 1
--   select_type: SIMPLE
--         table: orders
--    partitions: NULL
--          type: ref
-- possible_keys: idx_user_created_asc
--           key: idx_user_created_asc
--       key_len: 8
--           ref: const
--          rows: 4832
--      filtered: 100.00
--         Extra: Using index; Backward index scan

The Backward index scan in the Extra column tells you MySQL is traversing the ascending index in reverse. This is legal and works correctly, but it is less efficient than a forward scan — especially for large result sets — because InnoDB does not prefetch pages backward and lock acquisition is more complex in reverse traversal.

MySQL 8.0 with Descending Index

-- After adding: INDEX idx_user_created_desc (customer_id ASC, created_at DESC)
EXPLAIN SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE customer_id = 9801
ORDER BY created_at DESC
LIMIT 20\G

-- Output:
-- *************************** 1. row ***************************
--            id: 1
--   select_type: SIMPLE
--         table: orders
--    partitions: NULL
--          type: ref
-- possible_keys: idx_user_created_desc
--           key: idx_user_created_desc
--       key_len: 8
--           ref: const
--          rows: 20
--      filtered: 100.00
--         Extra: Using index

Three things changed: Backward index scan is gone, Using filesort is absent, and rows dropped from 4,832 to 20 — MySQL now stops at the LIMIT instead of scanning all matching rows. The rows estimate collapsing to the LIMIT value is a strong signal that the optimizer recognised the index satisfies the ORDER BY and enabled early termination.

Confirming with EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON
SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE customer_id = 9801
ORDER BY created_at DESC
LIMIT 20\G

-- Look for in the JSON output:
-- "access_type": "ref",
-- "key": "idx_user_created_desc",
-- "backward_index_scan": false,   -- absent or false = forward scan
-- "filesort": false               -- no sort step

Real-World Performance Impact

The measurable difference between a backward scan and a forward scan on a descending index depends on table size, index depth, and buffer pool hit rate. On a cold buffer pool — typical for dashboard queries that run infrequently against large tables — forward scans are substantially faster because InnoDB's read-ahead can prefetch the next pages predictably. On a warm buffer pool with the relevant index pages cached, the difference narrows but does not disappear entirely.

Benchmarking the Difference on a 10M-Row Table

-- Setup: 10M orders, 5,000 distinct customers, ~2,000 orders per customer
-- Index scenario A: idx_customer_created_asc (customer_id ASC, created_at ASC)
-- Index scenario B: idx_customer_created_desc (customer_id ASC, created_at DESC)

-- Warm the buffer pool (run the query 3x, measure the 3rd run)
-- Query: most recent 50 orders for customer_id = 2813

-- Scenario A (ascending index, backward scan):
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 2813
ORDER BY created_at DESC LIMIT 50;
-- ~4.1ms average (backward scan through ~2,000 index entries)

-- Scenario B (descending index, forward scan):
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 2813
ORDER BY created_at DESC LIMIT 50;
-- ~1.3ms average (forward scan stops at entry 50)

The improvement is approximately 3x on a warm buffer pool. On a cold buffer pool or when the LIMIT is large (e.g., LIMIT 500), the gap widens because the backward scan must acquire more pages and cannot leverage sequential prefetch. The improvement is most pronounced when combined with the early-termination benefit of LIMIT clauses.

The Mixed-Sort Filesort Elimination

For the mixed-sort-order case, the improvement is categorical rather than marginal. A filesort on 50,000 matching rows at 200 bytes per row requires a 10MB sort buffer — likely overflowing to disk. Replacing that with a forward index scan that terminates at LIMIT 50 reduces the query from O(n) to O(LIMIT), regardless of how many rows match the WHERE clause.

-- Verify filesort is gone for mixed-sort query
EXPLAIN SELECT id, status, created_at
FROM jobs
WHERE queue = 'notifications'
ORDER BY status ASC, created_at DESC
LIMIT 50;

-- Expected Extra (with correct mixed-direction index): Using index
-- Expected Extra (without correct index):             Using index; Using filesort

When NOT to Use Descending Indexes

Descending indexes are not universally beneficial. Adding them indiscriminately increases write overhead and index storage without improving query performance for queries that do not use that sort direction.

When the Query Always Uses ASC

If a column is only ever queried with ORDER BY col ASC, a descending index serves no purpose and forces every write to maintain an extra B-tree in reverse order. Profile your slow query log and application query patterns before adding any descending index.

When the Table is Write-Heavy and the Index is Rarely Used

Every INSERT, UPDATE, or DELETE that touches an indexed column must update all indexes on that column. A descending index doubles the index maintenance cost for that column. On a table that receives 10,000 inserts per second, adding a redundant descending index can measurably reduce write throughput. Use SHOW STATUS LIKE 'Handler_write%' and performance_schema to measure the impact before committing.

When Selectivity Makes the Index Irrelevant

If a WHERE clause matches more than 20–30% of the table, the optimizer will often prefer a full table scan over any index regardless of direction. A descending index on a low-selectivity column provides no query plan improvement and only adds write overhead. Check the rows estimate in EXPLAIN relative to the total row count before keeping any new index.

When the LIMIT Is Large

Early termination — the main benefit of a descending index with a LIMIT clause — matters less when the LIMIT is large (e.g., LIMIT 10000 for a batch export). For batch operations that read large portions of an index, the forward vs backward scan difference is smaller and the filesort cost may be acceptable relative to the write overhead of maintaining an extra index. Profile both paths before deciding.

Working with JusDB on MySQL Performance Tuning

JusDB tunes MySQL index strategies for engineering teams — identifying filesorts, adding descending indexes where beneficial, and monitoring query plan regressions after schema changes.

Explore JusDB MySQL Management →  |  Talk to a DBA

Related reading:

• MySQL Indexes Explained
• MySQL EXPLAIN: Query Optimization Guide
• MySQL Performance Schema: A Practical Guide