How to Speed Up MySQL Deep Pagination: 4 Proven Optimization Techniques

Introduction

When implementing pagination we often use LIMIT, but with a very large offset the query performance degrades sharply. This article discusses four solutions to optimize deep pagination on a MySQL table with millions of rows and provides a production case study.

Why LIMIT Deep Pagination Is Slow

Consider the following table definition:

CREATE TABLE account (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key Id',
  name varchar(255) DEFAULT NULL COMMENT 'Account name',
  balance int(11) DEFAULT NULL COMMENT 'Balance',
  create_time datetime NOT NULL COMMENT 'Creation time',
  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
  PRIMARY KEY (id),
  KEY idx_name (name),
  KEY idx_update_time (update_time)
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='Account table';

Typical deep‑pagination query:

SELECT id, name, balance FROM account WHERE update_time > '2020-09-19' LIMIT 100000,10;

This query takes about 0.742 seconds, while the same query with LIMIT 0,10 finishes in 0.006 seconds.

The execution flow is:

Use the secondary index idx_update_time to filter rows by update_time and obtain matching record IDs.

For each ID, look up the primary‑key index to fetch the full row (the “back‑table” step).

Scan 100 010 rows, discard the first 100 000, and return the last 10.

The slowdown is caused by two factors: LIMIT 100000,10 forces MySQL to scan 100 010 rows before discarding the offset rows, whereas LIMIT 0,10 scans only 10 rows.

Scanning more rows also increases the number of back‑table lookups.

Optimization via Subquery

Because the original query performs 100 010 back‑table lookups, we can reduce the number of lookups to just the needed 10 by moving the condition to the primary‑key index.

Review of B+‑Tree Indexes

In InnoDB there are two kinds of indexes:

Primary‑key (clustered) index stores the full row in leaf nodes.

Secondary index stores only the primary‑key value in leaf nodes.

Shift Condition to Primary‑Key Index

We rewrite the query so that the secondary index returns the primary‑key IDs, then use those IDs to fetch rows directly from the primary‑key index:

SELECT id, name, balance FROM account WHERE id >= (
  SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' LIMIT 100000,1
) LIMIT 10;

This version returns the same result in only 0.038 seconds.

INNER JOIN Delayed Association

The same idea can be expressed with an INNER JOIN instead of a subquery, further reducing back‑table accesses:

SELECT acct1.id, acct1.name, acct1.balance FROM account acct1
INNER JOIN (
  SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000,10
) AS acct2 ON acct1.id = acct2.id;

This query finishes in about 0.034 seconds.

Tag‑Record Method

The root cause of deep pagination is that a larger offset forces MySQL to scan and discard more rows. By recording the last processed primary‑key value (a “tag”), the next query can start from that point:

SELECT id, name, balance FROM account WHERE id > 100000 ORDER BY id LIMIT 10;

This approach works well when the primary key is monotonic, but it requires a continuously increasing column.

Using BETWEEN…AND

If the exact range of IDs is known, replace the offset with a range scan:

SELECT id, name, balance FROM account WHERE id BETWEEN 100000 AND 100010 ORDER BY id;

Practical Case Study

Assume a table account with 2 million rows:

CREATE TABLE account (
  id varchar(32) NOT NULL COMMENT 'Primary key',
  account_no varchar(64) NOT NULL DEFAULT '' COMMENT 'Account number',
  amount decimal(20,2) DEFAULT NULL COMMENT 'Amount',
  type varchar(10) DEFAULT NULL COMMENT 'Type A or B',
  create_time datetime DEFAULT NULL COMMENT 'Creation time',
  update_time datetime DEFAULT NULL COMMENT 'Update time',
  PRIMARY KEY (id),
  KEY idx_account_no (account_no),
  KEY idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Account table';

Business requirement: extract all type‑A accounts created in 2021 for reporting to a big‑data platform.

A naïve implementation would paginate with LIMIT, suffering from the deep‑pagination problem.

Optimized solution uses the tag‑record method: after each page, store the last id and query the next page with WHERE id > :lastId and ORDER BY id. This eliminates the large offset scan and keeps performance stable regardless of page number.

// Pseudocode
String lastId = queryMinId(); // get smallest id for the period
int pageSize = 100;
List<Account> list;
do {
  list = listAccountByPage(lastId, pageSize);
  lastId = list.get(list.size() - 1).getId();
  postBigData(list);
} while (!list.isEmpty());

// MyBatis mapper
SELECT * FROM account
WHERE create_time >= '2021-01-01 00:00:00'
  AND type = 'A'
  AND id > #{lastId}
ORDER BY id ASC
LIMIT #{pageSize};