How to Resolve Deep Pagination Performance Problems in MySQL

Incident Background

At 16:00 a colleague reported a drop in the availability of a pagination API in the integration system. Monitoring showed a sudden rise in the interface TP99 latency and abnormal CPU usage on many machines, prompting a detailed investigation.

Problem Timeline

16:05 – Interface monitoring revealed TP99 spikes.

16:10 – Machine‑level monitoring showed high TP999 values but normal CPU usage.

16:10 – Database CPU surged; numerous slow‑SQL logs appeared.

16:15 – Logs pointed to a merchant with ~100k rows (≈10% of total) and massive MQ retries; the pagination timeout was only 2 seconds.

16:25 – Code review identified a deep‑pagination issue; an initial fix rewrote the SQL to first fetch IDs, then query by ID range, while pausing upstream MQ consumption.

17:40 – Optimized code went live, but accumulated MQ messages still pressured the database, causing TP99 to rise again.

18:00 – Decision made to develop a new cursor‑based pagination API.

22:20 – New API launched, MQ consumption resumed, and the system stabilized.

Root Cause Analysis

The original SQL used SELECT * FROM table WHERE org_code = xxxx LIMIT 1000, 100, which forces MySQL to scan and discard the first 1000 rows before returning the next 100. As the offset grows, performance degrades sharply.

Solution Approaches

1) ID‑Based Query + IN

First fetch the required IDs, then retrieve rows with SELECT * FROM table WHERE id IN (1,2,3,4,5). This leverages the primary‑key index for fast lookups.

2) ID‑Based Range Query

After obtaining the ID list, use a range condition:

SELECT * FROM table WHERE org_code = xxxx AND id >= 1 AND id <= 5

. This avoids the overhead of an IN list.

3) Sub‑Query Join

Combine the two steps in a single statement:

SELECT a.id, a.dj_org_code, a.dj_sku_id, a.jd_sku_id, a.yn
FROM table a
JOIN (SELECT id FROM table WHERE org_code = xxxx LIMIT 1000,5) b
ON a.id = b.id;

Using a sub‑query reduces I/O and keeps the query set‑based.

4) Cursor (Scrolling) Pagination

Each request returns the maximum ID (cursor). The next request supplies this cursor, and the server selects rows with IDs greater than the cursor, e.g.:

SELECT * FROM table WHERE org_code = xxxx AND id > 0 LIMIT 10;

This method is simple to implement and performs well, provided IDs are monotonically increasing and results are ordered by ID.

Final Implementation

The team settled on the cursor‑based approach and refined the SQL. After testing, the optimized query was deployed:

SELECT id, dj_org_code, dj_sku_id, jd_sku_id, yn
FROM table
WHERE org_code = xxxx AND id > 0
ORDER BY id ASC
LIMIT 500;

Subsequent monitoring showed stable latency. However, a week later another surge of slow queries appeared. An EXPLAIN revealed that MySQL chose the primary‑key index over the org_code index because the LIMIT clause made the primary key more efficient, leading to a full‑table scan.

To force the intended index, the team used:

SELECT id, dj_org_code, dj_sku_id, jd_sku_id, yn
FROM table FORCE INDEX(idx_upc)
WHERE org_code = xxxx AND id > 0
ORDER BY id ASC
LIMIT 500;

While effective, hard‑coding index names can be risky if the schema changes.

Key Takeaways

Implement rate limiting on critical APIs to protect downstream systems from traffic spikes.

Do not ignore pagination performance warnings, even if current traffic seems low.

SQL optimizations must be tested across different data distributions and edge cases.