How to Fix Deep Pagination Slow Queries in MySQL: A Real Incident Walkthrough

Incident Background

This incident is a typical example of a pagination slow‑query problem encountered by our team. The article walks through the reconstruction of the incident scene and the adjustments made at each step.

Problem Timeline

16:00 – Received report that the fusion system's paginated query availability dropped.

16:05 – UMP monitoring showed an abnormal spike in interface TP99.

16:10 – Database monitoring revealed CPU usage soaring and a flood of slow‑SQL alerts.

16:15 – Logs indicated most slow SQLs targeted a merchant with ~100 k rows, causing high‑frequency retries.

16:25 – Code review identified a deep‑pagination issue and proposed an optimization: first query IDs, then fetch data by ID range.

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

18:00 – Decided to develop a new interface based on scrolling IDs instead of deep pagination.

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

Root Cause and Solutions

Why Deep Pagination Happens

Example SQL:

select * from table where org_code = xxxx limit 1000,100

MySQL’s LIMIT first reads the first 1000 rows, discards them, then returns the next 100. Larger offsets mean more rows are scanned, degrading performance.

Various Remedies

Query ID + ID‑Based Retrieval

First fetch the IDs, then query by those IDs.

select id from table where org_code = xxxx limit 1000,5

select * from table where id in (1,2,3,4,5)

Range Query Using IDs

After obtaining the IDs, use a range condition instead of IN for better efficiency.

select * from table where org_code = xxxx and id >= 1 and id <= 5

Sub‑query Approach

select a.id,a.dj_sku_id,a.jd_sku_id from table a join (select id from jd_spu_sku where org_code = xxxx limit 1000,5) b on a.id = b.id

Scrolling Query

Each request returns the maximum ID (cursor); the next request supplies this cursor to fetch the next batch.

select * from table where org_code = xxxx and id > 0 limit 10

Later optimized for non‑spec SKU:

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

Join‑Based Final Solution

First query IDs (without leaf fields) and then join to fetch full rows, ensuring the primary‑key index is used.

select a.id AS id,a.dj_org_code AS djOrgCode,a.dj_sku_id AS djSkuId,a.jd_sku_id AS jdSkuId,a.yn AS yn from table a join (SELECT id FROM table where org_code = xxxx and id > 0 order by id asc limit 500) t on a.id=t.id;

Additional Insights

MySQL may prefer the primary‑key index over a regular index when LIMIT is small relative to the result set.

Forcing an index works but ties the query to a specific index name, which can be risky.

Problem Summary

Backend systems need rate‑limiting to protect against abnormal traffic that can overload databases.

SQL optimizations must be thoroughly tested across edge cases, especially for merchants with large SKU counts.

Consistent monitoring and careful rollout are essential to avoid regressions.