How to Speed Up MySQL Deep Pagination on Millions of Rows

Problem

Using LIMIT offset, row_count for pagination causes MySQL to scan offset+row_count rows and then discard the first offset rows. This leads to large scans and many back‑to‑table lookups, especially on tables with millions of rows. Example:

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

takes 0.742 s, while LIMIT 0,10 takes 0.006 s.

Why it is slow

MySQL reads the secondary index (e.g., idx_update_time) to locate matching primary‑key IDs.

For each ID it performs a primary‑key lookup (back‑to‑table) to fetch the full row.

When the offset is large, MySQL scans many rows ( offset + n) and discards the first offset rows.

Optimization strategies

1. Subquery that moves the filter to 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;

Execution time reduced to ~0.038 s.

2. INNER JOIN (delayed association)

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

Execution time ~0.034 s.

3. Bookmark (tag‑record) pagination

Store the last processed primary‑key value and query the next page with a range condition.

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

Works when a monotonically increasing column (e.g., auto‑increment ID) is available.

4. BETWEEN … AND … range scan

If the start and end IDs are known, replace LIMIT with a BETWEEN predicate.

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

Practical case study

Table definition (simplified):

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

Business requirement: fetch all A‑type accounts created after 2021‑01‑01 for reporting. A naïve implementation uses COUNT to get total rows, calculates page numbers, and iterates with LIMIT offset, pageSize, which suffers from deep‑pagination slowdown.

Optimized implementation uses bookmark pagination:

// Get the smallest ID for the first page
String lastId = accountDAO.queryMinId();
int pageSize = 100;
List<AccountPO> list;
do {
    list = accountDAO.listAccountByPage(lastId, pageSize);
    // Record the last ID as the new bookmark
    lastId = list.get(list.size() - 1).getId();
    postBigData(list);
} while (!list.isEmpty());

Corresponding MyBatis XML:

<select id="queryMinId" resultType="java.lang.String">
    SELECT MIN(id)
    FROM account
    WHERE create_time >= '2021-01-01 00:00:00'
      AND type = 'A'
</select>

<select id="listAccountByPage" resultMap="accountMap">
    SELECT *
    FROM account
    WHERE create_time >= '2021-01-01 00:00:00'
      AND id > #{lastId}
      AND type = 'A'
    ORDER BY id ASC
    LIMIT #{pageSize}
</select>

This eliminates the large offset scan; query time remains low regardless of page number.

Conclusion

Deep pagination degrades performance because MySQL must scan and discard rows for large offsets, causing many back‑to‑table operations. Moving the filter to the primary‑key index—via subqueries, INNER JOIN, bookmark pagination, or BETWEEN range scans—reduces the number of scanned rows and yields order‑of‑magnitude speed improvements.