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

1. Problem Reproduction

In real software development, as the user base expands, table data grows; for example, an order table with ~40,000 orders per day reaches over 1.4 million rows per year. When the query does not use an index on key fields, performance degrades and can affect user experience and service stability.

Using a customer table from an e‑commerce system (MySQL, >1 million rows) we measured pagination performance for different offset values, returning at most 100 rows per query.

When offset is 0, time: 18 ms

When offset is 1,000, time: 23 ms

When offset is 10,000, time: 54 ms

When offset is 100,000, time: 268 ms

When offset is 500,000, time: 1.16 s

When offset is 1,000,000, time: 2.35 s

These results show that as the offset grows, pagination latency increases dramatically, reaching seconds for offsets above one million. In many companies, any SQL taking longer than 1 second is considered a slow query; some enforce stricter limits such as 0.2 seconds.

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

Industry studies indicate that average request times under 1 second provide the best user experience, 2 seconds are still acceptable, while exceeding 3 seconds degrades experience and over 5 seconds leads to abandonment.

2. Solutions

2.1 Solution 1: Return Only Primary‑Key ID

Replace

select *

with

select id

to reduce the amount of data transferred.

When offset is 100,000, time: 73 ms

When offset is 500,000, time: 274 ms

When offset is 1,000,000, time: 471 ms

By first paging the primary‑key IDs and then fetching the required columns, query latency improves dramatically.

<code>-- First page 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,...);
</code>

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 by ID.

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

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

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

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

2.3 Solution 3: Use ElasticSearch as Search Engine

When data volume grows further and sharding is introduced, filtering by primary key may no longer be sufficient. Storing the data in ElasticSearch enables fast pagination and full‑text search, dramatically improving response times.

3. Summary

Numeric primary keys are essential for efficient sorting; avoid UUIDs as primary keys because they hinder ordering and degrade performance. Choose generation strategies such as auto‑increment or Snowflake to keep IDs numeric and sortable.