This is a common pagination business requirement.
Executing the following SQL on a table with 2 million rows:
select * from table_name limit 1000000,5Execution time: 25s
The problem:
MySQL handles LIMIT OFFSET by fetching ALL rows (OFFSET+LIMIT), then discarding the OFFSET rows and returning only the bottom LIMIT rows. When the offset is very high — e.g., limit 100000,20 — the system queries 100,020 rows, then throws away the first 100,000. This is an extremely expensive operation that causes slow queries.
How to optimize:
Use id > m limit n instead of limit m, n. This is much faster because it leverages the primary key index and only queries n records. This approach works well for data loading but may not suit all frontend pagination scenarios — because IDs may not be contiguous, it only works for “escalator-style” pagination, not “elevator-style” pagination.
select * from table where id > 1000000 limit 5Execution time: 0.013s
Another simple optimization is to use a covering index query, then join with the full row. This allows you to use the index directly to find data without querying the full table. Once the needed rows are found, join with the full table to get the other columns:
select * from table_name inner join (select id from table_name limit 1000000,5) as tmp on tmp.id = table_name.idExecution time: 0.211s