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

1. Problem Reproduction

In large‑scale software systems, table data continuously grows, causing pagination queries to become increasingly slow. Using a MySQL customer table with over 1 million rows, the author measured query times for fetching 100 rows at various offset positions.

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

Offset 10,000,000: 39 s

These results show a dramatic increase in latency as the offset grows, turning what should be a sub‑second query into a multi‑second operation for million‑level tables.

2. Solutions

Solution 1 – Return Only Primary‑Key ID

Replace SELECT * with SELECT id to fetch only the primary‑key column, then retrieve the full rows in a second step. Timing after this change:

Offset 100,000: 73 ms

Offset 500,000: 274 ms

Offset 1,000,000: 471 ms

Fetching only the ID reduces the amount of data transferred and speeds up the query considerably.

Solution 2 – Filter by Primary‑Key ID Range

Use the primary‑key (numeric) to define a narrow range and query only that slice. Example timings:

Range 100,000–100,0100: 18 ms

Range 500,000–500,0100: 18 ms

Range 1,000,000–1,000,0100: 18 ms

This approach keeps query latency stable around 20 ms because the engine can use the index efficiently.

Solution 3 – Use ElasticSearch for Search‑Heavy Scenarios

When tables become extremely large or are sharded, even ID‑range filtering may be insufficient. Storing the data in ElasticSearch enables fast pagination and full‑text search, providing a noticeable performance boost for order‑type data.

-- First, fetch the IDs that satisfy the pagination condition
SELECT id FROM bizuser ORDER BY id LIMIT 100000, 10;

-- Then retrieve the full rows for those IDs
SELECT * FROM bizuser WHERE id IN (1,2,3,4,...);

3. Summary and Recommendations

Numeric primary‑key IDs are essential for efficient sorting and pagination; avoid using UUIDs as primary keys because they hinder index performance. Choose the appropriate solution based on business needs: simple ID‑only queries for most cases, ID‑range filtering for stable low‑latency access, or ElasticSearch when dealing with massive, distributed datasets.