QueryKings | Deep pagination in MySQL: why LIMIT 200000, 20 gets slower, and the keyset fix

A pagination query that returns in 3 ms on page one and 4 seconds on page eight thousand is not suffering from load, cache pressure, or a bad plan. It is doing exactly what you asked, and the cost is linear in the page number.

SELECT id, subject, created_at
FROM tickets
WHERE account_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000;

MySQL has no way to jump to the 200,001st matching row. It produces rows in order, counts 200,000 of them, discards every one, and returns the next 20. The work is proportional to the offset, so latency grows in a straight line as users (or a crawler, or an export script) walk deeper into the list. The last page is always the slowest one, and it is the page your monitoring notices last.

Confirm the mechanism before changing anything

Two checks separate "offset is the problem" from "the query was always slow."

First, run the same query at two depths and compare. If OFFSET 0 is fast and OFFSET 200000 is slow by roughly the ratio of the offsets, you have offset cost, not a plan problem.

Second, use EXPLAIN ANALYZE on 8.0 and later and read the row counts, not the time:

EXPLAIN ANALYZE
SELECT id, subject, created_at
FROM tickets
WHERE account_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000;

You are looking for a Limit node whose child reports actual rows=200020. That line is the whole diagnosis: 200,020 rows produced, 20 kept. If the plan also shows Sort (or plain EXPLAIN shows Using filesort), you have a second, compounding problem — the server is materializing and sorting the entire matching set before it can even start counting. In that case there is an index fix available first, and it is worth taking regardless of how you paginate.

Step one: make the sort index-ordered

Keyset pagination only helps if the index can deliver rows in the order you want. The same index removes the filesort:

ALTER TABLE tickets
  ADD INDEX idx_account_created_id (account_id, created_at, id);

Equality column first, then the sort columns in sort order. InnoDB can read a secondary index backwards, so a DESC ordering on this index is fine; where you mix directions (created_at DESC, priority ASC) you need a descending index on 8.0, which stores the key in the order you declared:

ALTER TABLE tickets
  ADD INDEX idx_account_created_prio (account_id, created_at DESC, priority ASC);

On 5.7, DESC in an index definition was parsed and ignored, which is one more small reason the 8.x upgrade pays for itself in query work.

With the index in place, OFFSET 200000 still reads 200,020 index entries, but it reads them in order and stops. That alone often turns a 4-second query into 200 ms. It is a real improvement and it is not the fix.

Step two: the keyset rewrite

Keyset pagination — also called seek pagination — replaces "skip N rows" with "start after this specific row." The client sends back the sort key of the last row it saw, and the query seeks straight to it:

SELECT id, subject, created_at
FROM tickets
WHERE account_id = 42
  AND (created_at, id) < ('2026-03-14 09:31:02', 88431)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The row-constructor comparison is the part people get wrong when they write it by hand. Do not expand it into created_at < ? OR (created_at = ? AND id < ?) unless you have to: MySQL 8.0 optimizes the row-constructor form into a proper index range scan, while the OR form frequently degrades into a broader scan. If you are on 5.7, write the OR form explicitly and check the plan.

Two requirements make this correct rather than approximately correct:

The sort key must be unique. created_at alone is not; two tickets created in the same second will duplicate on one page and vanish from the next. Appending the primary key makes the tuple unique and total. This is the single most common bug in hand-rolled keyset code.
The key columns must match the index prefix order. (account_id, created_at, id) supports seeking on (created_at, id) within an account. It does not support seeking on (priority, id).

The result is constant-time paging. Page 1 and page 40,000 cost the same: one index dive, twenty rows. Latency stops depending on depth, and it stops depending on how big the table got last quarter.

What keyset pagination costs you

It is an interface change, not a query change, and that is the real bill:

No jump-to-page. You can offer next and previous, not "page 4,812." For an infinite-scroll feed or an API cursor, nobody notices. For an admin table with a page-number strip, it is a product conversation.
No total count for free. SELECT COUNT(*) over the same predicate is its own expensive scan. Often the honest answer is to drop the exact count, or show an approximate one from information_schema.TABLES for unfiltered lists, or cap it ("1,000+ results").
The cursor must be opaque and encoded. Ship the sort tuple as a base64 cursor token rather than raw column values in the URL. It keeps the client from constructing keys you do not support, and it lets you change the sort key later without breaking bookmarks.

When you cannot drop jump-to-page

Sometimes the page-number strip stays. Three options, in the order we usually recommend them.

Deferred join. Keep OFFSET, but pay it on the narrow index instead of the wide row:

SELECT t.id, t.subject, t.body, t.created_at
FROM tickets t
JOIN (
  SELECT id
  FROM tickets
  WHERE account_id = 42
  ORDER BY created_at DESC, id DESC
  LIMIT 20 OFFSET 200000
) AS k USING (id)
ORDER BY t.created_at DESC, t.id DESC;

The subquery is covered by idx_account_created_id and never touches the clustered index, so skipping 200,000 entries reads index pages only. The outer query does twenty primary-key lookups. This is still linear in the offset, but the constant shrinks by an order of magnitude or more on wide rows with large TEXT or BLOB columns. It is a rewrite with no product impact, which makes it the easiest win in this whole article.

Bounded offset. Allow deep offsets only inside a keyset window: fetch the cursor for the start of a page range, then offset a few hundred rows from there. This covers "jump ahead a bit" without covering "jump to the end."

Cap the depth. Reject offsets beyond a fixed ceiling with a clear error and a nudge toward filtering or sorting differently. Unattractive, and frequently correct: past page 500, the traffic is almost always a scraper or a script that should be using an export endpoint or a replica.

The related failure: deep paging against replicas

Export jobs and back-office reports that page through a whole table with OFFSET are a common cause of replica lag and buffer-pool churn. Each successive page evicts more useful data to re-read rows it will discard. Converting those jobs to keyset iteration on the primary key — WHERE id > ? ORDER BY id LIMIT 5000 — usually removes both the lag spikes and the cache damage, and it makes the job restartable from its last cursor instead of from the beginning.

A short checklist

Reproduce at two offsets; confirm cost scales with depth.
EXPLAIN ANALYZE and look for a Limit node over a child reporting offset-plus-limit actual rows.
Add the composite index so the sort is index-ordered, not a filesort.
Rewrite user-facing lists and API endpoints as keyset pagination with a unique sort tuple and an opaque cursor.
Where page numbers must stay, use a deferred join and cap the maximum depth.
Convert batch and export jobs to primary-key keyset iteration with a bounded chunk size.

Steps 1 through 3 are usually an afternoon. Steps 4 and 5 are a sprint item because they touch the API contract. Doing 3 first buys the time to do 4 properly, which is the order we recommend when the page is on fire.

If your slow-query digest is topped by a list endpoint with a large OFFSET, that pattern is worth an hour of review before it becomes an outage. Send us the statement, the SHOW CREATE TABLE, and the plan, and we will tell you which of the fixes above applies.