Why MySQL Pagination Slows Down at Scale and How to Speed It Up

1. Problem Reproduction

In real software development, as the user base grows, the amount of data in a single table increases over time. For an order table with about 40,000 orders per day, the monthly volume exceeds 1.2 million and the yearly volume exceeds 14 million. As the table grows, queries that do not use indexes become slower, affecting user experience and service stability.

Using a customer table from an e‑commerce system as an example (MySQL, over 1 million rows), we test pagination query performance when returning at most 100 rows per request with different offset values.

Offset 0: 18 ms

Offset 1,000: 23 ms

Offset 10,000: 54 ms

Offset 100,000: 268 ms

Offset 500,000: 1.16 s

Offset 1,000,000: 2.35 s

The results clearly show that as the offset increases, pagination query efficiency drops dramatically, reaching seconds for offsets above one million.

In practice, any SQL query taking longer than 1 second is considered a slow query; some organizations even set the threshold at 0.2 seconds and require immediate optimization.

For a table with tens of millions of rows, a pagination query with offset 10,000,000 took 39 seconds in our test.

Considering the full request chain (database, backend processing, network, and front‑end rendering), a 1‑second database query can result in a total response time of 3–4 seconds, which exceeds the optimal user‑experience threshold of 1 second.

When single‑table data reaches the million‑level, query efficiency sharply declines, prompting the need for optimization.

2. Solutions

Below are three concrete solutions.

2.1 Solution 1: Return Only Primary Key ID

Replace select * with select id to reduce the amount of data transferred.

Offset 100,000: 73 ms

Offset 500,000: 274 ms

Offset 1,000,000: 471 ms

By simplifying the returned fields, query performance improves dramatically.

The practical approach is to first paginate the primary‑key IDs that meet the condition, then fetch the required columns using those IDs.

-- First paginate the primary key IDs
select id from bizuser order by id limit 100000, 10;

-- Then fetch the full rows for those IDs
select * from bizuser where id in (1,2,3,4, ...);

2.2 Solution 2: Filter by Primary Key ID

This method requires the primary key to be numeric. Use the maximum ID from the previous page as the filter condition, ensuring the sort column is the primary key.

Query IDs 100,000–100,0100: 18 ms

Query IDs 500,000–500,0100: 18 ms

Query IDs 1,000,000–1,000,100: 18 ms

Using the primary‑key ID as a filter yields stable performance, typically within 20 ms.

If sorting requirements are minimal, this solution is highly effective.

2.3 Solution 3: Use Elasticsearch as Search Engine

When data volume grows and sharding becomes necessary, primary‑key filtering may not suffice (e.g., order data). Storing such data in Elasticsearch enables fast pagination and search, significantly improving performance.

3. Summary

Numeric primary‑key IDs are preferred because they support efficient sorting. String‑type primary keys such as UUIDs hinder sorting and degrade search performance, so they are not recommended. Common numeric ID generation strategies include auto‑increment, Snowflake algorithm, etc.