MySQL Descending Indexes: Mixed-Direction Composites Done Right

The slowest user-facing query in a support product I ran was the agent queue: open tickets for one tenant, ordered by priority ascending and creation time descending, fifty rows at a time. On MySQL 5.7 no index could serve that ORDER BY, because the directions were mixed, so every page load ran a filesort over the tenant's open tickets — 1.6 seconds at p95 for our biggest customer, and climbing with their growth. On 8.0, one composite index with a descending part took it to 4 milliseconds, and the sort simply stopped existing. Descending indexes are that rare feature that deletes a whole category of workaround.

Here is what actually changed in 8.0, why the pre-8.0 reverse-scan trick only solved half the problem, what a backward index scan really costs on current hardware, how I design mixed-direction composites for feeds and leaderboards, and which EXPLAIN lines prove the index is doing the sort.

What did DESC in an index definition do before MySQL 8.0?

Nothing, and it did it silently. MySQL 5.7 accepted DESC in an index definition, parsed it, and threw it away — the index was built ascending in every part regardless, and nothing warned you. The only ordering an index could serve without a filesort was uniform: all columns ascending, or all columns descending by scanning an all-ascending index backwards. The moment an ORDER BY mixed directions — priority ASC, created_at DESC — the answer was a filesort, always, no matter how you arranged the DDL. Teams worked around it with generated columns holding negated numbers, reversed timestamps, and other contortions I would rather forget. If you maintain old schema dumps, look at any index declared with DESC on a 5.7 system and assume it is lying to you.

How does a mixed-direction composite resolve ORDER BY without filesort?

InnoDB stores secondary index records in declared key order, so when you declare (priority ASC, created_at DESC), every leaf page is physically sorted exactly the way the query wants its output. The optimizer reads the first fifty index entries in storage order and stops — the sort is the storage layout. The queue index looked like this:

CREATE TABLE tickets (
  ticket_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  status ENUM('open','pending','closed') NOT NULL,
  priority INT NOT NULL DEFAULT 3,
  created_at DATETIME(6) NOT NULL,
  subject VARCHAR(200) NOT NULL,
  PRIMARY KEY (ticket_id),
  KEY queue_idx (tenant_id, status, priority ASC, created_at DESC)
) ENGINE=InnoDB;

Two properties follow. The same index also serves the fully reversed ordering — priority DESC, created_at ASC — because InnoDB can scan it backwards. But it cannot serve priority ASC with created_at ASC, or priority DESC with created_at DESC: mixed orderings must match the index's mixed directions exactly, or you are back to filesort. Direction is part of the contract. Write the ORDER BY first and design the index to it, never the other way around.

What does a Backward index scan actually cost?

A backward scan walks leaf pages right to left and follows the prev pointers between pages instead of the next pointers, and it gives up some of the forward-optimized readahead. The honest cost on cached data is single-digit percent in my benchmarks — real, measurable, and almost never worth engineering around. On cold or spinning storage the gap widens, because linear readahead is built around forward traversal, but even there a backward scan over a selective range beats a filesort of the full matching set by a wide margin. My position: for all-descending orderings, take the backward scan of your ascending composite and do not build a redundant mirrored index unless EXPLAIN ANALYZE proves the scan itself is the bottleneck. On modern NVMe it nearly never is — the random-read penalty that made scan direction matter in the disk era has shrunk to noise for most working sets.

There are also cases where a new descending composite is the wrong move. Every additional index is write amplification on each insert and update, plus buffer pool and disk footprint, so if an existing ascending index already serves the query with a backward scan and the table is write-heavy, I leave it alone. I also see teams add a DESC index for a query that runs twice a day against ten thousand rows; the filesort they eliminated cost 30 milliseconds. Descending indexes pay when the query is hot, the range is large, or the sort spills to disk — measure the sort first with EXPLAIN ANALYZE, then spend the index where the numbers argue for it.

How should you design composites for feeds and leaderboards?

Feeds first. The pattern is equality columns up front, ordering columns after, directions matched to the ORDER BY, and a tie-breaker that keeps pagination stable: (user_id, created_at DESC, id DESC). I keep the id tie-breaker descending too, because then keyset pagination stays a single row-tuple comparison — (created_at, id) < (?, ?) — while a mixed direction forces the cursor predicate to be written out the long way, an OR chain with one branch per column. The long way is valid SQL and a reliable source of off-by-one bugs, so I only pay it when the ordering truly demands it. The keyset pagination note covers the cursor side of that contract in detail.

Leaderboards are the same shape with more aggression: (season_id, score DESC, kills DESC, player_id). Make it covering — add every column the query selects, or trim the SELECT list to what the index carries — and the query never touches the clustered index at all. A covering composite with the right directions removes both costs that dominate leaderboard reads: the filesort and the per-row lookup back to the primary key. On a 4-million-row scores table, that combination took our top-100 endpoint from 900 milliseconds to under 10, and most of the win came from the covering part rather than the sort part.

Which EXPLAIN notes prove the index is doing the sort?

Three lines tell the story. The absence of Using filesort is the headline: if the ORDER BY resolves from the index, filesort does not appear at all. Backward index scan appears when InnoDB walks the index in reverse to satisfy the ordering — an informational note, not a warning. Using index marks the covering case, where the query is answered entirely from the secondary index.

EXPLAIN
SELECT ticket_id, priority, created_at
FROM tickets
WHERE tenant_id = 42 AND status = 'open'
ORDER BY priority ASC, created_at DESC
LIMIT 50;
-- type: ref, key: queue_idx
-- Extra: Using index   -- and no Using filesort

EXPLAIN
SELECT ticket_id, priority, created_at
FROM tickets
WHERE tenant_id = 42 AND status = 'open'
ORDER BY priority DESC, created_at ASC
LIMIT 50;
-- Extra: Backward index scan; Using index

If you still see Using filesort next to an index you built for this exact ORDER BY, check directions first, then column order, then whether the optimizer picked a different index entirely — the classic trap documented in ORDER BY LIMIT picking the wrong index. And when the plan looks right but the latency does not, measure it with the workflow in the EXPLAIN ANALYZE guide before touching the schema again.

Where MonPG stands on MySQL

I build MonPG, so plainly: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. Sort behavior is one of the most valuable things to watch over time — filesort-heavy digests climbing after a deploy, a rebuilt index quietly flipping a scan's direction — and the MySQL work is designed to surface exactly that from performance_schema digests. The MySQL monitoring (coming soon) page is where it lands. Meanwhile the same monitoring philosophy runs on the PostgreSQL side today, and more MySQL field notes are on the blog.