SQL Pagination: Why Page 100 Is Slower Than Page 1 (and How to Fix It)

Have you ever built a paginated list that was fast in testing, but got slower the further users scrolled?

It's a common problem, and the query behind it usually looks fine. In this article, you'll learn why it happens, and how to write a paginated query that's just as fast on page 100 as it is on page 1.

The Usual Way: OFFSET Pagination

Let's say we have an orders table, and we want to show 10 orders per page, sorted by order ID.

Most paginated queries are written like this:

1SELECT order_id, customer_id, order_date, order_total
2FROM orders
3ORDER BY order_id
4OFFSET 990 ROWS FETCH NEXT 10 ROWS ONLY;

It reads almost like plain English: skip 990 rows, then give me the next 10. That's page 100.

The syntax is a little different depending on your database, but they all work the same way:

If you want more detail on these keywords, see my guide to SQL LIMIT and row limiting.

This query returns the right rows. So what's the problem?

Why OFFSET Gets Slower on Every Page

The problem is how the database finds those rows.

To skip 990 rows, the database has to read them first. It walks through the index from the start, counting rows as it goes, until it reaches row 991. Then it returns the next 10, and throws away the 990 it just read.

So the amount of work grows with every page:

You always get 10 rows back, but the database does more work each time.

It's like being asked for page 50 of a book with no page numbers. You have to flip through from the start and count.

On a small table in a test environment, you won't notice. On a table with millions of rows, where users (or an API client, or a search engine crawler) go deep into the list, those later pages can get slow.

The Fix: Keyset Pagination

The fix is called keyset pagination. You may also see it called seek pagination or cursor-based pagination.

Instead of telling the database how many rows to skip, you tell it where to start.

Your application holds onto the last value from the previous page, and passes it into the next query. If the last order on the previous page had an ID of 2345, the next page is:

1SELECT order_id, customer_id, order_date, order_total
2FROM orders
3WHERE order_id > 2345
4ORDER BY order_id
5FETCH FIRST 10 ROWS ONLY;

In MySQL, use LIMIT 10 instead of FETCH FIRST 10 ROWS ONLY. In SQL Server, use SELECT TOP 10.

The first page is the same query without the WHERE clause.

Now the database can use the index on order_id to jump straight to 2345, and read the next 10 rows from there. It does the same amount of work on page 100 as it does on page 1.

There's another benefit too. With OFFSET, if a new row is inserted or deleted while someone is paging through, rows can shift between pages. The user might see the same row twice, or miss one. Keyset pagination starts from a known value, so this doesn't happen.

Paging on a Column That Isn't Unique

The example above uses order_id, which is unique. But what if you want to sort by something that isn't unique, like order_date?

If several orders have the same date, a filter like WHERE order_date > '2026-03-14' would skip the remaining orders on that date. Some rows would never appear.

The fix is to add a unique column as a tie-breaker, and filter on both columns:

1SELECT order_id, customer_id, order_date, order_total
2FROM orders
3WHERE order_date > '2026-03-14'
4   OR (order_date = '2026-03-14' AND order_id > 2345)
5ORDER BY order_date, order_id
6FETCH FIRST 10 ROWS ONLY;

Your application now remembers two values from the last row: the order_date and the order_id.

MySQL and PostgreSQL also support a shorter way to write this, called a row value comparison:

1SELECT order_id, customer_id, order_date, order_total
2FROM orders
3WHERE (order_date, order_id) > ('2026-03-14', 2345)
4ORDER BY order_date, order_id
5LIMIT 10;

SQL Server and Oracle don't support this syntax, so use the OR version there.

For this to be fast, you need an index that matches the sort order:

1CREATE INDEX idx_orders_date_id ON orders (order_date, order_id);

Without a matching index, the database still has to sort the table to find your starting point, and you lose most of the benefit.

The Trade-Off

Keyset pagination has one main limitation: you can't jump to an arbitrary page number.

To get page 50, you need the last value from page 49. There's no way to say "give me page 50" directly.

For a lot of interfaces, that doesn't matter:

• Infinite scroll
• "Load more" buttons
• Next and previous buttons
• APIs that return the next batch of results with a cursor

These all move one page at a time, so keyset pagination is a great fit.

But for a screen where someone types in a page number, or clicks on "page 37", OFFSET is still the right tool. In that case, it's worth checking how deep people actually go. Most users rarely go past the first few pages, so OFFSET may be fine for your data.

Which One Should You Use?

Here's a quick summary:

So, the next time you write a paginated query for a list that users will scroll through, check whether it's skipping rows or starting from a known point. If it's skipping rows, it'll get slower as your data grows.

If you have any questions about pagination, let me know in the comments below.