Why MySQL LIMIT Pagination Slows Down and How to Speed It Up with Covering Indexes

Background

A single table on a personal website contains about 866,633 rows (≈4.1 GB). Accessing pages with large page numbers caused severe slow‑downs and time‑outs in Google Analytics.

Test Environment

Metadata of the table was inspected with basic SQL statements:

use information_schema;
SELECT * FROM TABLES WHERE TABLE_SCHEMA='dbname' AND TABLE_NAME='product';

The result shows:

Row count: 866,633

Average row length: 5,133 bytes (≈5 KB)

Table size: 4,448,700,632 bytes (≈4.1 GB)

The table contains various column types (varchar, datetime, text, etc.) and id is the primary key.

Experiment: Direct LIMIT Pagination

Queries were executed with the classic LIMIT start, count pattern used in the original program. Execution times were measured for different start offsets (20 rows per page):

SELECT * FROM product LIMIT 10,20;      -- 0.016 s
SELECT * FROM product LIMIT 100,20;     -- 0.016 s
SELECT * FROM product LIMIT 1000,20;    -- 0.047 s
SELECT * FROM product LIMIT 10000,20;   -- 0.094 s
SELECT * FROM product LIMIT 400000,20;   -- 3.229 s
SELECT * FROM product LIMIT 866613,20;   -- 37.44 s

The execution time grows roughly linearly with the offset because MySQL must scan and discard all rows before the requested start position.

Conclusions

The query time of a LIMIT statement is directly proportional to the position of the starting record.

Although convenient, MySQL’s plain LIMIT pagination is unsuitable for very large tables when deep pages are required.

Optimization Using a Covering Index

A covering index (an index that contains all columns referenced by the query) allows MySQL to satisfy the query using only the index structure, avoiding full row reads. The primary‑key column id already provides such an index.

Fetching only the id column for the last page demonstrates the speed gain:

SELECT id FROM product LIMIT 866613,20;   -- 0.2 s

This is about 100 × faster than selecting all columns (37.44 s).

Retrieving Full Rows Efficiently

Sub‑query to obtain the starting id and then fetch the next 20 rows:

SELECT * FROM product
WHERE id >= (SELECT id FROM product LIMIT 866613,1)
LIMIT 20;   -- ≈0.2 s

Join with a derived table that contains the desired id values:

SELECT p.*
FROM product p
JOIN (SELECT id FROM product LIMIT 866613,20) AS ids
  ON p.id = ids.id;   -- ≈0.2 s

Both approaches leverage the primary‑key index, delivering a qualitative performance boost while still returning complete rows.

Key Takeaways

Use a covering index (e.g., the primary key) for pagination to avoid full table scans.

When deep pages are needed, first retrieve the key values (via LIMIT on the indexed column) and then join or filter on those keys.

Keyset pagination (e.g., WHERE id > last_id ORDER BY id LIMIT n) is an alternative that provides constant‑time page access.