Mastering MySQL Deep Pagination: 6 Proven Optimization Techniques

Introduction

The author recounts a late‑night incident where an e‑commerce order‑query API slowed from 200 ms to 12 seconds, with MySQL CPU usage exceeding 90%, caused by a classic deep‑pagination query on the user’s historical orders.

Deep Pagination Problem

When the offset grows, MySQL must scan and discard an ever‑increasing number of rows, turning a simple LIMIT 100000,10 query into a costly full‑table scan.

CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_userid_create_time` (`user_id`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Typical pagination query:

SELECT * FROM orders
WHERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 0, 20;

Querying page 1000 (offset 19980):

SELECT * FROM orders
WHERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 19980, 20;

Use the composite index idx_userid_create_time to read 19980 + 20 rows.

Sort in memory using the index.

Discard the first 19980 rows and return the remaining 20.

As the offset reaches 100 k or 1 M, query time grows dramatically, causing severe latency spikes.

Cursor‑Based Pagination

Ideal for continuous scrolling; it relies on a unique ordered column (e.g., auto‑increment id) and the last record of the previous page to avoid OFFSET.

-- 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 OFFSET, reducing time complexity from O(N) to O(1).

Naturally supports infinite‑scroll scenarios.

Limitations: cannot jump to an arbitrary page and requires a unique, ordered column.

Deferred Join

First fetch primary keys in a sub‑query, then join to the main table, minimizing row look‑ups.

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 scans only the index, avoiding table reads.

Main query matches by primary key, yielding high efficiency.

Measured performance gain up to tenfold (1.2 s → 0.05 s).

Covering Index Optimization

Create a composite index that contains all columns required by the query, so MySQL can satisfy the request directly from the index.

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

Split a massive table horizontally by time (e.g., monthly partitions) to limit the scan range.

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)
);
SELECT * FROM orders PARTITION (p202501)
WHERE user_id = 'chaya'
ORDER BY create_time DESC
LIMIT 20;

Precomputed Pages

Generate hot‑page data asynchronously and store it in Redis (or a materialized view) for instant retrieval.

ZADD order_pages 0 "page1_data" 1000 "page2_data"

ZRANGEBYSCORE order_pages (N-1)*1000 N*1000

Elasticsearch Integration

Leverage Elasticsearch’s search_after feature to implement deep pagination, syncing MySQL binlog to ES via Canal and Kafka.

orders → Binlog → Canal → Kafka → Elasticsearch, HBase

During a query, retrieve order IDs from ES and then fetch the full rows from MySQL or HBase, achieving low‑latency deep pagination for massive datasets.