Apr 3, 2026

MySQL Pagination Performance Guide: Why Do Later Pages Get Slower?

Pagination appears in almost every list screen. But a common MySQL performance surprise is that later pages become slower and slower. In many systems, the core issue is the cost of OFFSET-based pagination.

In this post, we will cover:

why pagination queries get slower
the limits of LIMIT ... OFFSET ...
when another approach is a better fit
how to think about the tradeoff in practice

The key idea is that pagination is not only a UI problem. It is directly tied to how many rows MySQL has to skip and read.

Why do later pages get slower?

Consider a query like:

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

To return those 20 rows, MySQL may still have to walk through many earlier rows first. As the offset grows, the amount of skipped work can grow too.

What are the limits of OFFSET pagination?

OFFSET pagination is simple and familiar, which makes it appealing early on. But it also has real downsides:

deeper pages can get slower
changing sort order can make rows feel duplicated or missing
inefficiency grows at larger scale

So it is convenient, but not always a great long-term fit.

What is a common alternative?

A common alternative is cursor-based pagination.

Instead of saying “give me page 50,” you say something closer to:

give me rows after this last seen created_at and id

That means the system continues from a known position rather than skipping a huge number of rows.

Why can cursor pagination be better?

Cursor-style access often reduces the cost of deep pagination. It also tends to behave more consistently when the ordering is well defined.

But it has tradeoffs too:

it may not map naturally to numbered page UIs
ordering design matters a lot
implementation can be more complex

So the decision is usually about both performance and product experience.

How should you choose between them?

OFFSET can be a good fit when:

data volume is still modest
deep-page navigation is rare
numbered page UX matters a lot

Cursor pagination can be a good fit when:

the dataset is large
deep navigation is common
infinite scroll or “load more” is a natural UX

Why do indexes matter here too?

Pagination is still built on sorting and range access, so indexes matter a lot. If the ordering column and filter pattern do not match the index design, performance can degrade even faster.

So pagination is not only about SQL syntax. It is also about ordering strategy and index support.

Common misunderstandings

1. LIMIT means the query is always cheap

Not if the database still has to skip a huge number of rows first.

2. Pagination is mostly a frontend concern

In practice, it is a database access pattern and query cost issue too.

3. Cursor pagination is always better

It can be better for performance, but it is not always the best UX fit.

FAQ

Q. Can cursor pagination work with numbered page UIs?

Sometimes, but the design gets more complex.

Q. What should I inspect first when deep pages are slow?

Start with offset size, ordering, and index alignment.

Q. Should small apps care about this early?

Simple approaches are fine at first, but fast-growing datasets make this worth understanding early.

Start Here

Continue with the core guides that pull steady search traffic.