How to Speed Up MySQL LIMIT Pagination for Millions of Rows

Method 1: Direct LIMIT

SQL: SELECT * FROM table_name LIMIT M,N Suitable for small tables (hundreds or thousands of rows). Performs a full‑table scan, results may be unstable because LIMIT discards rows after the result set is built.

Method 2: Primary‑Key/Unique Index

SQL:

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

Uses an index scan, fast for large tables (tens of thousands of rows). May miss rows if the result set is not ordered by the primary key.

Method 3: Index‑Based Sorting

SQL:

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

The ORDER BY column is the primary key, allowing MySQL to use the index for sorting and producing a stable result set.

Method 4: Prepared Statements

SQL:

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

The first placeholder is the page number, the second is the page size. Works well for very large data sets and is slightly faster than a plain query.

Method 5: ORDER‑by Index Scan (Covering Index)

Example:

SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

By selecting only the indexed column (the primary key), MySQL can satisfy the query using the covering index, reducing execution time from dozens of seconds to a few hundred milliseconds.

Method 6: Subquery/Join + Index

Subquery example:

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

Join example:

SELECT * 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;

Experimental Results

Benchmarking a table named product (millions of rows) with a page size of 20:

limit 10,20 → 0.016 s

limit 100,20 → 0.016 s

limit 1 000,20 → 0.047 s

limit 10 000,20 → 0.094 s

limit 400 000,20 → 3.229 s

limit 866 613,20 (last page) → 37.44 s

The query time grows roughly linearly with the offset, confirming that plain LIMIT becomes impractical for deep pagination.

Optimization Techniques

Use a covering index (select only indexed columns) to avoid accessing the full row data.

When full rows are needed, first retrieve the primary‑key IDs via an indexed range, then join back to the table to fetch the remaining columns.

Design composite indexes with the WHERE‑condition columns first and the primary‑key column second; select only the indexed columns to keep the query in the index‑only path.

For queries with a WHERE clause, place the filtered column at the leading position of the composite index to ensure the index is used.

Conclusion

Plain LIMIT pagination is inefficient for large offsets because MySQL must scan and discard many rows. Proper index design—especially covering indexes and composite indexes that put the filtered column before the primary key—can reduce pagination time from dozens of seconds to fractions of a second, even on tables with millions of rows.