Why MySQL LIMIT Slows Down on Large Offsets and How to Fix It

MySQL LIMIT pagination becomes slower as the offset grows because the engine must scan rows up to the starting position, leading to full‑table scans and unstable result ordering.

Method 1: Direct LIMIT

SQL: SELECT * FROM table_name LIMIT M, N; Scenario: Small data sets (hundreds to thousands of rows).

Drawback: Full‑table scan; performance degrades with larger offsets.

Method 2: Primary Key or Unique Index

SQL:

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

Scenario: Large data sets (tens of thousands of rows).

Note: Index scan is fast, but ordering issues may appear if results are not sorted by the primary key.

Method 3: Index with ORDER BY

SQL:

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

Scenario: Large data sets; ensures stable ordering when the ordered column is the primary key or a unique index.

Method 4: Prepared Statement

SQL:

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

Scenario: Very large data sets; prepared statements add a small speed gain over regular queries.

Method 5: Covering Index

SQL: SELECT id FROM product LIMIT 866613, 20; Result: Query time drops from 37.44 s (selecting all columns) to 0.2 s, a >100× improvement because only the indexed column is read.

Method 6: Subquery/Join with Index

Subquery example:

SELECT * FROM product WHERE id >= (SELECT id FROM product ORDER BY id DESC LIMIT $page-1, $pagesize) LIMIT $pagesize;

Join example:

SELECT * FROM product a JOIN (SELECT id FROM product LIMIT 866613, 20) b ON a.id = b.id;

Result: Both queries finish in ~0.2 s, showing that joining a small indexed result set avoids scanning the whole table.

Composite Index Optimization

When a WHERE clause is required, the index must place the filter column first and the primary‑key column second. Selecting only the primary key allows MySQL to use the composite index efficiently, turning a multi‑second query into a sub‑second one.

Conclusion: Design indexes so that the WHERE columns appear first, the primary‑key second, and limit the SELECT list to the indexed columns. This strategy enables fast pagination even on tables with millions of rows.