How to Fix MySQL Deep Pagination Bottlenecks with 6 Proven Techniques

Preface

Hello, I’m Su San. One night around 11 PM the operations group of an e‑commerce platform erupted with alerts: the order‑query API response time spiked to 12 seconds and MySQL CPU usage exceeded 90 %. The root cause was a seemingly ordinary query – a historical order pagination request – exposing the classic MySQL deep‑pagination problem where data retrieval slows down dramatically as the offset grows.

The essence of deep pagination is the traditional LIMIT offset, count pattern, e.g.,

LIMIT 100000,10

, which forces MySQL to scan and discard a huge number of rows before returning the desired page.

Deep Pagination

Assume an orders table with 20 million rows, primary key

id

and a composite index on

user_id, create_time

:

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;

A typical pagination query for the first page looks like:

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

When requesting page 1000 (offset 19980), the query becomes:

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

Execution steps:

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 grows, the amount of data scanned grows linearly, causing query time to explode when offsets reach hundreds of thousands.

Cursor‑Based Pagination

Use case: continuous scrolling (infinite scroll).

Implementation: rely on a unique, ordered column (e.g., auto‑increment ID) and remember the last ID of the previous 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;

The index can jump directly to the leaf node for

order_id=1000

, scanning only the following rows and eliminating the need to traverse the first million rows.

Advantages

Avoids OFFSET scans; time complexity drops from O(N) to O(1).

Naturally supports sequential pagination scenarios.

Limitations

Cannot jump to an arbitrary page number.

Requires the ordering column to be unique and ordered.

Deferred Join

Implementation: first fetch primary‑key ranges via a subquery, then join back to the main table, reducing 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;

Advantages

The subquery scans only the index tree, avoiding row‑fetch overhead.

The outer query matches rows by primary key, yielding very high efficiency.

Performance gains of up to 10× (e.g., from 1.2 s to 0.05 s) have been observed.

Covering Index Optimization

Implementation: create a composite index that includes all columns needed by the query, so MySQL can satisfy the request from the index alone without touching the table.

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

Implementation: split a large table horizontally by time or hash. For example, partition by month:

-- Range partition by month
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

Implementation: generate hot‑page data asynchronously and store it in Redis or a materialized view, suitable for low‑frequency update scenarios.

Steps

Scheduled job creates data for frequently accessed pages.

Store the data in a Redis sorted set.

ZADD order_pages 0 "page1_data" 1000 "page2_data"

When querying, fetch the cached page directly.

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

Elasticsearch Integration

Implementation: use Elasticsearch’s

search_after

feature to achieve deep pagination via a cursor, keeping data in sync with MySQL through Canal + Kafka pipelines.

Data flow: orders → Binlog → Canal → Kafka → Elasticsearch/HBase.

During a query, first retrieve order IDs from Elasticsearch, then fetch the full rows from MySQL (or HBase).

After applying the above optimizations, the order‑query API response time dropped to around 200 ms.