Why MySQL LIMIT Pagination Slows Down and 6 Proven Optimization Techniques

Direct LIMIT

SELECT * FROM your_table LIMIT M,N

Suitable for small tables (hundreds‑thousands rows).

Performs a full‑table scan; result order not guaranteed; performance degrades linearly with offset.

Primary‑Key / Unique Index

SELECT * FROM your_table WHERE id_pk > (pageNum*10) LIMIT M

Best for larger tables (tens of thousands rows).

Uses an index scan, fast, but if the result set is not ordered by the primary key rows can be missed.

Index with ORDER BY

SELECT * FROM your_table WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M

Ordering by an indexed column lets MySQL use the index for both filtering and sorting.

MySQL currently supports only ASC for this pattern.

Prepared Statements

PREPARE stmt_name FROM 'SELECT * FROM your_table WHERE id_pk > (?*?) ORDER BY id_pk ASC LIMIT M'

Useful for very large data sets; small performance gain over plain queries.

ORDER‑by Index Shortcut

Read rows 1000‑1019 directly using the primary‑key index:

SELECT * FROM your_table WHERE id_pk >= 1000 ORDER BY id_pk ASC LIMIT 20;

Sub‑query / Join with Index

Sub‑query version (find the start position with a nested SELECT):

SELECT * FROM your_table
WHERE id <= (
    SELECT id FROM your_table ORDER BY id DESC LIMIT ($page-1)*$pagesize
)
ORDER BY id DESC
LIMIT $pagesize;

Join version (join outer table with sub‑query that returns the start id):

SELECT t1.* FROM your_table AS t1
JOIN (
    SELECT id FROM your_table ORDER BY id DESC LIMIT ($page-1)*$pagesize
) AS t2 ON t1.id <= t2.id
ORDER BY t1.id DESC
LIMIT $pagesize;

Benchmark Experiments

Table product with millions of rows, each query returns 20 rows:

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

Time grows roughly linearly with the offset, confirming that a plain LIMIT with a large offset forces MySQL to scan many rows.

Optimization Findings

Using a covering index (selecting only the indexed column) reduces the 37 s query to about 0.2 s (>100× speed‑up).

When full rows are needed, two approaches achieve ~0.2 s:

Filter by id >= (SELECT id … LIMIT …) then apply LIMIT.

Join the table with a sub‑query that returns the start id.

A plain LIMIT on a non‑indexed column (e.g., WHERE vtype=1) becomes extremely slow (8‑9 s) even with an index on vtype, because MySQL cannot use the index for the offset.

Creating a composite index with the filter column first and the primary‑key second (e.g., INDEX(vtype, id)) allows MySQL to use the index for both filtering and pagination, reducing query time to a few hundredths of a second.

Practical Recommendations

Design pagination queries to use a covering index whenever possible – select only indexed columns.

If a WHERE clause is required, create a composite index with the filter column(s) first and the primary‑key (or another unique column) second.

Avoid large offsets with plain LIMIT; instead remember the last retrieved primary‑key value and query with WHERE id > last_id.

Consider prepared statements for repeated pagination queries on massive tables.

When the dataset exceeds a few hundred thousand rows, evaluate sharding or a dedicated index table that stores only the columns needed for pagination.

With these techniques MySQL can paginate millions of rows in sub‑second time, turning a naïve limit‑based pagination into a fast, scalable solution.