Why OFFSET/LIMIT Pagination Is Inefficient and How to Implement High‑Performance Cursor‑Based Pagination

The days of not having to worry about database performance optimization are gone.

With the progress of the times, ambitious companies aiming to become the next Facebook, and the desire to collect as much data as possible for machine‑learning predictions, developers must continuously polish their APIs to provide reliable and efficient endpoints that can effortlessly browse massive data.

If you have done backend development or database schema design, you may have paginated like this:

If you really paginate like that, I’m sorry to say you are doing it wrong.

Not convinced? No problem. Companies such as Slack , Shopify and Mixmax use the pagination method we will discuss today.

You’ll find it hard to locate anyone who does not use OFFSET and LIMIT for database pagination. For small applications and modest data volumes this approach can still “cope”.

If you want to build a reliable and efficient system from the ground up, you need to get it right from the start.

Today we will explore the problems of the widely used pagination method and how to achieve high‑performance pagination.

1. What problems do OFFSET and LIMIT have?

As mentioned earlier, OFFSET and LIMIT are fine for projects with small amounts of data.

However, when the amount of data in the database exceeds what the server memory can hold and you need to paginate over all data, problems arise.

To paginate, the database must perform an inefficient full‑table scan for each pagination request.

What is a full‑table scan? A full‑table (or sequential) scan reads every row in a table and checks each column against the query condition. This is the slowest type of scan because it incurs massive disk I/O and large memory‑to‑disk transfer overhead.

This means that if you have 100 million users and OFFSET is 50 million, the database has to fetch all those records (including many that are not needed), load them into memory, and then apply LIMIT to retrieve the 20 rows you asked for.

In other words, to get a page of data you have to first fetch 50 000 rows. How inefficient is that?

If you don’t believe me, see this example:

https://www.db-fiddle.com/f/3JSpBxVgcqL3W2AzfRNCyq/1

The left side shows the Schema SQL that inserts 100 000 rows; the right side contains a poorly performing query and a better solution. Clicking Run compares their execution times. The first query runs at least 30 times slower than the second.

The more data, the worse the situation. See my PoC on 100 000 rows.

https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC

Now you should understand what happens: the higher the OFFSET , the longer the query time.

2. Alternative approaches

You should do this:

This is a pointer‑based pagination.

You store the last primary key you received (usually an ID) and LIMIT , instead of OFFSET and LIMIT , so each query might look like this.

Why? Because by explicitly telling the database the latest row, it knows exactly where to start searching (using an effective index) without scanning records outside the target range.

Compare this query:

and the optimized version:

Both return the same result; the first query took 12.80 seconds while the optimized one took only 0.01 seconds.

Cursor‑based pagination requires a unique sequential field (or multiple), such as a unique integer ID or timestamp, though in some cases this may not be possible.

My advice is to consider the pros and cons of each solution and decide which query to execute.

If you need to query massive data sets, Rick James’s article provides deeper guidance.

http://mysql.rjweb.org/doc.php/lists

If a table lacks a primary key, such as a many‑to‑many relationship table, you can fall back to the traditional OFFSET/LIMIT method, but be aware of potential slow queries. I recommend using an auto‑increment primary key in tables that need pagination, even if only for that purpose.