MySQL ORDER BY LIMIT and the Wrong Index Problem

There is one plan regression I have now debugged on so many MySQL systems that I can call it from the incident title alone: a listing page or API endpoint that was fast for a year is suddenly taking thirty seconds, nothing deployed, and the query ends in ORDER BY created_at DESC LIMIT 20. Somewhere between yesterday and today, the optimizer stopped using the index on the WHERE clause and started walking the index on the ORDER BY column instead.

This is not a bug, exactly. It is a cost-model bet that is brilliant when it wins and catastrophic when it loses, and the losing conditions are data-dependent, which is why it detonates without a deploy. MySQL is notorious for this particular flip, though it is not alone; I compared how the two engines handle it in ORDER BY LIMIT regressions in MySQL vs PostgreSQL. This post is the MySQL-side field manual: why the optimizer makes the bet, how to see it, and the fixes in order of durability.

The bet the optimizer is making

Take the canonical shape:

SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

With an index on status and an index on created_at, there are two ways to run this. Plan A uses the status index: fetch every pending order, sort them by created_at, return 20. Its cost scales with the number of pending rows, and it pays for a filesort. Plan B walks the created_at index backward from the newest row, checking each row's status, and stops after collecting 20 matches. No sort at all, and if pending orders are common and spread evenly through time, it might touch only a few dozen rows.

That even-spread assumption is the entire bet. The optimizer reasons: if 5 percent of rows are pending, scanning the ordered index should find 20 matches after roughly 400 rows. LIMIT is what makes plan B look cheap, because the cost is prorated by the expected early exit. But the proration assumes matching rows are uniformly distributed along the ordering column, and real data is not uniform. If pending orders are concentrated in old data, or the filter matches almost nothing today, or the query filters on a customer with no recent orders, plan B walks millions of index entries, doing a random-ish row lookup for each, before finding 20 matches. In the worst case, fewer than 20 matches exist, and it walks the entire table. The failure is data-dependent: the same query is instant for one customer and minutes for another, which is why these incidents look haunted.

Seeing the flip for what it is

Tabular EXPLAIN shows the signature if you know to look: key is the ordering index rather than the filter index, no filesort in Extra, and often a suspiciously optimistic rows figure. But the unambiguous evidence is EXPLAIN ANALYZE, where you see the backward index scan iterator with actual rows in the millions above a filter that discarded nearly all of them, against an estimate of a few hundred. In the slow log with log_slow_extra, the tell is a Rows_examined figure five or six orders of magnitude above Rows_sent on a query ending in LIMIT 20. That ratio on a LIMIT query is this pattern until proven otherwise; your slow log digest will surface it as a family whose variance exploded while its median stayed flat.

prefer_ordering_index: the switch that admits the problem

MySQL 8.0.21 added an optimizer_switch flag that exists specifically because of this pattern: prefer_ordering_index, on by default. Set to off, it removes the optimizer's willingness to swap to the ordering index for LIMIT queries, forcing it to plan as if the LIMIT proration did not exist.

-- Statement-scoped: the surgical option
SELECT /*+ SET_VAR(optimizer_switch = 'prefer_ordering_index=off') */
       id, customer_id, total, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

Used per statement via SET_VAR, it is the cleanest emergency fix available: it names the exact pathology, documents itself in the query text, and touches nothing else. Disabling it globally is a heavier call. Plenty of queries genuinely benefit from the ordering-index strategy, chronological feeds with permissive filters being the obvious class, and a global off trades one family of regressions for another. My rule: statement-scoped immediately during the incident, then a durable fix, and global changes only if audit shows the workload has no queries that win the bet.

Older lore suggests defeating the optimizer by wrapping the ORDER BY column in an expression, ORDER BY created_at + INTERVAL 0 DAY, or using IGNORE INDEX. They work, but they are riddles left for the next maintainer; the hint says what it means.

The durable fix is a composite index

The reason this query forces a bad choice is that no index serves both the filter and the order. Give it one and the dilemma evaporates:

CREATE INDEX idx_status_created ON orders (status, created_at);

With equality on status, InnoDB jumps to the pending range of the index, within which entries are already ordered by created_at. The engine reads 20 entries from the end of that range and stops. No filesort, no bet, no scan, and the plan is stable regardless of how skewed the data becomes. Column order is the part people get wrong: equality-filtered columns first, then the ordering column. An index on (created_at, status) does not help this query at all. Watch the details that silently break the free ordering: mixing ASC and DESC across multiple ORDER BY columns needs matching index directions (8.0 supports DESC index columns properly), and a range predicate on an intermediate column ends the index's usefulness for ordering at that point.

The trade is real but usually acceptable: another index costs write amplification and space, and on a table with heavy insert traffic you should confirm the win covers it. For a query that backs a product surface, it almost always does.

Pagination patterns that do not re-arm the trap

These queries are usually page one of a paginated list, and OFFSET pagination makes every part of this worse. LIMIT 20 OFFSET 100000 must produce and discard 100,020 rows in the best case, the LIMIT-proration math shifts as the offset grows so plans can flip between pages, and the deep pages are exactly where the ordering-index bet loses hardest.

Keyset pagination, also called seek pagination, sidesteps all of it. Remember the last row of the previous page and ask for rows strictly after it:

SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'pending'
  AND (created_at, id) < ('2026-07-01 08:14:09', 91882274)
ORDER BY created_at DESC, id DESC
LIMIT 20;

With the composite index from above extended to (status, created_at, id), every page is a bounded index range read: seek, read 20 entries, stop, identical cost for page 1 and page 5000. The id tiebreaker matters twice over, making the sort deterministic and the cursor unambiguous when timestamps collide. One honest caveat: row-value comparisons like (a, b) < (x, y) are not always optimized into a tight index range in MySQL, so check the plan; the expanded OR form, created_at less than the cursor, or equal with id below it, is uglier but reliably index-friendly. Keyset cannot jump to an arbitrary page number, which is a product conversation worth having, because infinite scroll and next/previous cover most real interfaces.

Where MonPG fits

The defining feature of this regression is that it ships silently and detonates on a data condition, which makes it a monitoring problem as much as an indexing one: the fix is knowing the day the plan flipped, with the before and after evidence in hand. MonPG is a PostgreSQL monitoring platform that does exactly this for Postgres today, and we are building MySQL monitoring (coming soon) to catch the MySQL version of it, the Rows_examined explosions and variance spikes this post taught you to find by hand. MonPG does not monitor MySQL yet, plainly stated. If your stack also includes PostgreSQL, where ORDER BY LIMIT has its own sharp edges, the platform is live and you can start there.