Apr 10, 2026

Last updated on Apr 13, 2026

MySQL Pagination Performance Guide: What to Check Before You Blame OFFSET

Pagination feels like a basic UI feature, but once datasets grow, it becomes a real database access strategy. At first, LIMIT ... OFFSET ... feels perfectly fine. Then traffic grows, users reach deeper pages, infinite scroll gets added, multiple sort options appear, and suddenly pagination is no longer just about page buttons. It becomes about how many rows MySQL has to walk through, sort, and discard before it can return the next 20 results.

In MySQL, the most common performance issue is that OFFSET gets more expensive as pages go deeper. And that problem often connects directly to unstable ordering, duplicate-or-missing-looking results, COUNT(*) overhead, and index design.

This guide focuses on the practical questions:

why later pages get slower
what LIMIT ... OFFSET ... really costs
when keyset or cursor pagination is a better fit
how to choose a stable ordering key
why total-count UX can become expensive
how index design changes pagination cost

The short version is this: pagination cost is usually less about how many rows you return and more about how many rows MySQL must read and skip to reach them.

MySQL Pagination Performance Concept

Quick answer

In practice, a useful pagination decision flow looks like this:

check whether users actually go deep into later pages
confirm whether large OFFSET values are forcing MySQL to read and discard many rows
verify that the ordering key is stable and deterministic
if the UX is “load more” or infinite scroll, strongly consider keyset or cursor pagination
if numbered pages are required, decide whether deep-page cost and exact COUNT(*) are worth it
design indexes that support both filtering and ordering
validate with EXPLAIN and real latency measurements

So the core question is not only “how do we display pages?” It is how cheaply we can reach the next slice of data.

1. Why later pages get slower

Consider this query:

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

It only returns 20 rows, but that does not mean the work is small. MySQL may still need to walk through many earlier rows before it can reach the requested slice.

That is the key issue with deep OFFSET pagination:

the result size stays small
the skipped work keeps growing

So LIMIT does not automatically make the query cheap. A large OFFSET can hide a lot of discarded read cost.

2. Why OFFSET pagination is both convenient and expensive

OFFSET-based pagination has real advantages:

it is easy to implement
it maps naturally to numbered page UIs
it keeps early product work simple

But it also has real downsides:

deep pages can become slow
ongoing inserts or updates can make rows appear duplicated or missing between pages
exact count support often adds another expensive query
efficiency tends to degrade as the dataset grows

So OFFSET is not “wrong.” It is just a strategy whose weaknesses become much more visible at scale.

3. What keyset or cursor pagination changes

Cursor-style pagination changes the question from “which page number?” to “what comes after the last row we saw?”

For example:

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-04-13 09:00:00', 1234)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Instead of skipping 10,000 rows, the query continues from a known position.

That is the heart of the difference:

OFFSET says “skip a lot, then return a little”
keyset says “continue from this exact place”

That is why deep-page behavior is often dramatically better with keyset-style access.

4. Why cursor pagination often wins on deep datasets

The biggest strength of cursor pagination is that it avoids repeatedly paying for deep skips.

It is especially strong for:

infinite scroll
“load more” buttons
feeds ordered by time
event logs and activity lists

In these products, users usually do not care about “page 57.” They care about seeing what comes next. That is exactly where cursor pagination aligns well with both UX and performance.

So the reason cursor pagination helps is not because it is trendy. It helps because the database can keep moving forward instead of recounting a long skipped prefix over and over.

5. But cursor pagination is not free either

Cursor-based access has its own tradeoffs.

It does not naturally fit numbered-page UX

Users who expect “jump to page 7” may not map cleanly to a cursor model.

Ordering must be stable

If your order is not deterministic, cursor pagination becomes fragile.

The implementation is more involved

You need to manage encoded cursor state, forward and backward movement, and tie-breakers.

So cursor pagination is often a strong performance choice, but it is also a product and implementation choice, not only a database choice.

6. Why stable ordering matters so much

One of the most overlooked pagination bugs is unstable ordering.

If you sort only by created_at, multiple rows may share the same timestamp. Then a later query can return rows in a slightly different order, which makes users think rows were duplicated or skipped.

That is why real pagination often needs a deterministic tiebreaker, for example:

ORDER BY created_at DESC, id DESC

This matters especially for cursor pagination, because the “last seen position” has to be defined precisely.

So pagination performance is not just about index usage. It is also about whether the order is stable enough to paginate reliably.

7. Why `COUNT(*)` can become surprisingly expensive

Numbered page UIs often want total counts like:

12,842 results
643 pages

That usually means an additional count query.

The problem is that the count can be expensive too, especially when:

the filter is complex
joins are involved
the table is large
the list screen is visited frequently

In some systems, the count query becomes more painful than the page query itself.

That is why real systems sometimes choose alternatives:

approximate counts
count only on the first page
async count refresh
removing exact total-page UX in favor of “load more”

So pagination design is not only about fetching page rows. It is also about whether exact total count is worth the cost.

8. Index design is still central

Pagination is built on sorting and range access, so indexes matter a great deal.

Suppose your pattern looks like:

WHERE user_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 20

In that case, indexing only user_id may not be enough. A better index shape may need to reflect the ordering path too.

The practical questions are:

do filtering and ordering appear together repeatedly?
is deep-page cost being driven by the sort path?
does the keyset predicate align well with the index order?

So pagination performance often depends less on the LIMIT/OFFSET syntax itself and more on whether the sort-and-filter access path is well supported.

For the design side of that, pair this with the MySQL Index Design Guide.

9. Sometimes the biggest improvement is rethinking the product requirement

This is still a database topic, but in practice pagination performance often improves most when the product requirement is revisited.

Ask questions like:

do users really go to page 500?
would search or filtering reduce deep navigation?
would “load more” feel more natural than numbered pages?
does exact total count really matter here?

Teams sometimes pay a constant deep-page and count cost for UX features that almost nobody uses.

So pagination performance is also a UX design question, not just a SQL question.

10. A practical way to choose

OFFSET fits well when:

the dataset is still modest
deep-page navigation is rare
numbered page UX matters a lot
implementation simplicity matters more than long-tail efficiency

Cursor or keyset fits well when:

the dataset is large
users go deep or keep scrolling
“load more” or infinite scroll is natural
time-ordered exploration is central to the product

Hybrid strategies are often realistic too

For example:

admin tables use OFFSET
user-facing feeds use cursor pagination

There is often no single perfect strategy. The better answer is usually matching the pagination model to the screen and traffic pattern.

11. Common misunderstandings

1. `LIMIT` means the query is always cheap

Not if a large OFFSET still forces MySQL to walk through many earlier rows first.

2. Pagination is mostly a frontend concern

In practice, it is a database access and row-read cost issue too.

3. Cursor pagination is always better

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

4. Sorting only by `created_at` is good enough

Not always. Ties can create unstable paging behavior unless you add a deterministic tiebreaker.

FAQ

Q. If we need numbered pages, does that rule out cursor pagination?

Not completely, but the design becomes more complex. OFFSET usually fits numbered pages more naturally.

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

Start with offset size, ordering stability, index alignment with the sort path, and whether count queries are also contributing cost.

Q. Should small apps care about this early?

Starting simple with OFFSET is usually fine. But if the dataset will grow quickly or the product is feed-like, it is worth understanding keyset pagination early.

Start Here

Continue with the core guides that pull steady search traffic.