Why MySQL Pagination Slows Down on Large Tables and How to Fix It

1. Problem Reproduction

During software development, as the user base expands, the amount of data in a single table grows rapidly. For example, an order table with about 40,000 orders per day reaches over 1.4 million rows per year, and query performance degrades sharply when indexes are not used.

Using a customer table (MySQL, >1 million rows) as an example, we test pagination performance by retrieving up to 100 rows at various offsets.

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 grows, pagination performance degrades dramatically, reaching seconds‑level latency for offsets above one million.

In many organizations, any SQL taking longer than 1 second is labeled a “slow SQL”; some teams even enforce a 0.2 second threshold and require immediate optimization.

When the offset reaches 10 million, the query took 39 seconds, illustrating the severe impact on user experience, especially when combined with backend processing and front‑end rendering.

2. Solutions

Let's explore concrete optimization methods.

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 first fetching only the IDs that satisfy the pagination condition and then retrieving the full rows by those IDs, query speed improves dramatically.

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

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

2.2 Solution 2: Filter by Primary‑Key ID Range

This approach requires a numeric primary key. Use the maximum ID from the previous page as the lower bound and keep the ordering column as the primary key.

Query 100,000–100,100: 18 ms

Query 500,000–500,100: 18 ms

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

Using the primary‑key range keeps query latency stable around 20 ms, making it a highly reliable solution when sorting requirements are minimal.

2.3 Solution 3: Use Elasticsearch as a Search Engine

When data volume grows to the point where sharding is required, filtering by primary key may no longer be sufficient. Storing the data in Elasticsearch enables fast pagination and full‑text search, delivering a noticeable performance boost.

3. Summary

Numeric primary keys are essential for efficient sorting and pagination; avoid UUIDs as primary keys because they hinder ordering and degrade performance. Techniques such as returning only IDs, range‑based filtering, or offloading queries to a search engine like Elasticsearch can dramatically improve query speed on large tables.