Mastering MySQL Deep Pagination: 6 Proven Optimization Techniques

Introduction

A sudden alert on an e‑commerce platform revealed that the order‑query API latency jumped from 200 ms to 12 seconds and the database CPU usage exceeded 90 %. The culprit was a seemingly ordinary query: a historical order pagination request in the user‑center, exposing the classic MySQL deep‑pagination problem where query speed degrades dramatically as the offset grows.

Deep Pagination Problem

When using a traditional LIMIT offset, such as LIMIT 19980, 20 to fetch page 1000 (20 rows per page) from a table with 20 million rows, MySQL must scan the first 199,800 rows before returning the desired 20 rows. As the offset reaches 100 k or 1 M, the query time can increase from milliseconds to several seconds because the engine reads and discards a massive amount of data.

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 COLLATE=utf8mb4_unicode_ci;

Typical pagination query:

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

Deep‑page query (page 1000):

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

1. Cursor‑Based Pagination (Keyset Pagination)

Suitable for continuous scrolling scenarios. It relies on a unique, ordered column (e.g., the auto‑increment primary key) and uses the last retrieved key to fetch the next page, eliminating the OFFSET scan.

-- 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;

Time complexity drops from O(N) to O(1).

Naturally supports infinite‑scroll use cases.

Limitation: random page jumps are not supported; the ordering column must be unique and ordered.

2. Deferred Join (Covering Index Subquery)

First fetch the primary‑key range via a subquery that uses only the index, then join back to the table to retrieve full rows. This reduces the number of full‑table lookups.

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;

Subquery scans only the index tree, avoiding costly row fetches.

Main query matches primary keys directly, yielding high efficiency.

Performance gains of 10× observed (1.2 s → 0.05 s).

3. Covering Index Optimization

Create a composite index that includes all columns needed by the query, so MySQL can satisfy the request from the index alone without accessing 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;

4. Partitioned Tables

Split the massive table horizontally by time (e.g., monthly RANGE partitions). Each partition stores a subset of data, dramatically shrinking the scan range for time‑based queries.

-- 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;

5. Precomputed Pages (Cache)

For data that changes infrequently, generate pagination results offline (e.g., via a scheduled job) and store them in Redis sorted sets or materialized views. Queries then read directly from the cache.

ZADD order_pages 0 "page1_data" 1000 "page2_data"

Fetching a page (pseudo‑code): ZRANGEBYSCORE order_pages (N-1)*1000 N*1000 Cache hit returns pre‑computed rows instantly.

6. Elasticsearch Integration

Leverage Elasticsearch’s search_after cursor to achieve deep pagination on a search index. Data is synchronized from MySQL to Elasticsearch using Canal + Kafka pipelines, ensuring near‑real‑time consistency.

orders → Binlog → Canal → Kafka → Elasticsearch, HBase

During a request, first obtain the order IDs from Elasticsearch, then fetch the full rows from MySQL (or directly from HBase if fully synced).

Conclusion

MySQL deep pagination can cripple performance on large tables. By replacing OFFSET scans with cursor pagination, deferred joins, covering indexes, partitioning, precomputed caches, or an Elasticsearch‑backed search layer, latency can be reduced from seconds to milliseconds, keeping the e‑commerce platform responsive even under massive data volumes.