Boost MySQL Pagination: Fast Alternatives to LIMIT OFFSET

Using LIMIT with OFFSET for pagination becomes slow when navigating beyond many pages (e.g., over 10,000), because the database must scan rows sequentially and then skip them.

Optimization ideas

1. From a business perspective, avoid allowing users to jump past a reasonable page limit (most search engines cap around 70 pages).

2. Technically, avoid large OFFSET values. For example, SELECT * FROM table LIMIT 5000000,10 can take more than 4 seconds.

Optimization method 1

Use an indexed column to filter rows directly: SELECT * FROM table WHERE id > 5000000 LIMIT 10. This runs in about 0.02 seconds because it uses the index on id. This approach requires that id values are continuous and not deleted.

Optimization method 2

If you must keep LIMIT OFFSET, apply a delayed join (index covering): SELECT id FROM table LIMIT 5000000,10 This query only scans the id column, achieving an index‑only scan and thus fast execution.

Then retrieve the full rows with an inner join:

SELECT t.* FROM table t INNER JOIN (SELECT id FROM table LIMIT 5000000,10) AS tmp ON t.id = tmp.id

The join fetches the detailed information for each paged record while preserving the performance benefits.