From 12 s to 200 ms: 6 MySQL Deep‑Pagination Tricks for 20 M Orders

Deep Pagination

The article starts with a real incident: an e‑commerce order‑list API slowed from 200 ms to 12 s and CPU usage spiked above 90 % because the query used a classic LIMIT 19980, 20 on a table holding 20 million rows. The author explains that the underlying problem is the offset scan: MySQL must read and discard the first offset rows before returning the requested page, causing linear growth in I/O and CPU as the page number increases.

Cursor‑based Pagination

When to use : scenarios that require continuous scrolling (e.g., infinite scroll).

How it works : use a unique, ordered column (such as the auto‑increment id) and remember the last row’s key. The next page adds a WHERE id > last_id clause, allowing the index to jump directly to the start of the next page.

-- first page
SELECT * FROM orders
WHERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 20;

-- subsequent page (last id = 1000)
SELECT id, user_id, amount
FROM orders
WHERE id > 1000 AND user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 20;

Eliminates the OFFSET scan; time complexity drops from O(N) to O(1).

Naturally fits sequential paging use‑cases.

Limitations :

Cannot jump to an arbitrary page number.

Requires the ordering column to be unique and indexed.

Deferred Join (Delayed Join)

Principle : first fetch only the primary‑key values of the target page via a sub‑query, then join back to the main table to retrieve the full rows. This reduces the number of row look‑ups because the sub‑query scans only the index.

SELECT t1.*
FROM orders t1
INNER JOIN (
    SELECT id
    FROM orders
    WHERE user_id = 'Chaya'
    ORDER BY create_time DESC
    LIMIT 1000000, 20
) t2 ON t1.id = t2.id;

Sub‑query touches only the index tree, avoiding a full table scan.

Main query uses a precise primary‑key match, yielding very high efficiency.

Measured performance gain: >10× (from 1.2 s down to 0.05 s).

Covering Index Optimization

Principle : create a composite index that contains all columns needed by the query, so MySQL can satisfy the request directly from the index without touching the table rows.

ALTER TABLE orders ADD INDEX idx_cover (user_id, id, create_time, amount);

SELECT id, user_id, amount, create_time
FROM orders USE INDEX (idx_cover)
WHERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 1000000, 20;

Partitioned Table

Principle : split the large table horizontally by a range (e.g., month) so each partition stores a smaller subset of rows. Queries that include the partition key scan only the relevant partition.

-- monthly RANGE partitioning
ALTER TABLE orders PARTITION BY RANGE (YEAR(create_time)*100 + MONTH(create_time)) (
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p202502 VALUES LESS THAN (202503)
);

-- query a specific month
SELECT * FROM orders PARTITION (p202501)
WHERE user_id = 'chaya'
ORDER BY create_time DESC
LIMIT 20;

Precomputed Pages (Precomputed Pagination)

Principle : run an asynchronous job that generates the result set for hot pages in advance and stores them in Redis (or a materialized view). Suitable when data changes infrequently.

Scheduled task creates page data and writes it to a Redis sorted set.

At request time, the API reads the cached page directly.

ZADD order_pages 0 "page1_data" 1000 "page2_data"

-- pseudo‑code to fetch page N
ZRANGEBYSCORE order_pages (N-1)*1000 N*1000

Elasticsearch Integration

Principle : sync MySQL order data to Elasticsearch using Canal + Kafka. Elasticsearch’s search_after feature provides a cursor that can efficiently paginate deep results.

orders table → Binlog → Canal → Kafka → Elasticsearch / HBase

During a query, Elasticsearch returns the order IDs for the requested page; the application then fetches the full rows from MySQL (or HBase) by primary key.

All six techniques are demonstrated with concrete SQL snippets, performance numbers, and trade‑offs, giving readers a step‑by‑step toolbox to turn a 12‑second deep‑pagination query into a sub‑200‑millisecond response.