MySQL Deep Pagination Optimization

Pagination is a common backend feature. It is needed because users rarely view data far beyond the first few pages and because retrieving large data sets consumes server resources such as disk, memory, and network bandwidth.

MySQL pagination syntax example: SELECT * FROM table LIMIT 0, 20;

A test table t1 is created and populated with more than 4 million rows using a series of INSERT statements and a loop that generates random data.

CREATE TABLE t1 ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 'primary key', m_id BIGINT NOT NULL COMMENT 'other id', `name` VARCHAR(255) COMMENT 'user name', identity_no VARCHAR(30) COMMENT 'ID number', address VARCHAR(255) COMMENT 'address', create_time TIMESTAMP NOT NULL COMMENT 'created time', modify_time TIMESTAMP NOT NULL COMMENT 'modified time', PRIMARY KEY (id) ) ENGINE=INNODB DEFAULT CHARSET='utf8' COMMENT='deep pagination test table';

Several experiments were performed:

No WHERE clause and no ORDER BY: SELECT id,m_id,name,identity_no,address,create_time,modify_time FROM t1 LIMIT 1000000, 20; took 0.613 s.

Adding ORDER BY primary key: SELECT id,m_id,name,identity_no,address,create_time,modify_time FROM t1 ORDER BY id LIMIT 1000000, 20; reduced time to 0.417 s.

ORDER BY a non‑indexed column (create_time) on a table without an index: SELECT id,m_id,name,identity_no,address,create_time,modify_time FROM t1 ORDER BY create_time DESC LIMIT 10000, 20; took 2.015 s.

Adding an index on the sorting column (create_time) and querying the same SQL on table t2 reduced time to 0.937 s.

For deep pagination, forcing the index with FORCE INDEX(idx_create_time) still required 15 s, showing that reading >1 M rows is costly even with an index.

Using a “last_*” condition (keyset pagination) such as SELECT ... FROM t2 WHERE id > #{last_id} AND create_time > #{last_create_time} ORDER BY create_time DESC LIMIT 0, 20; performed as fast as shallow pagination, but only works for forward‑only navigation.

Transforming the forced‑index query into a sub‑query join: SELECT id,m_id,name,identity_no,address,create_time,modify_time FROM t2 JOIN (SELECT id FROM t2 ORDER BY create_time DESC LIMIT 1000000, 20) x USING (id); reduced execution time to 0.742 s (original >15 s).

The sub‑query works faster because it limits the sort buffer to only the indexed columns, avoiding large temporary files and extensive “Using filesort”.

Additional tuning such as increasing sort_buffer_size showed minimal impact on a personal PC.

Conclusions :

Always add an ORDER BY on the primary key when no other condition exists.

Index the columns used for sorting.

For deep pagination, consider keyset pagination (last_*), forced index with sub‑query, or two‑step queries to avoid full scans.

Adjust sort_buffer_size only as a minor optimization.

Avoid cross‑column indexes that do not match the query pattern.