Why Are MySQL Queries with Large LIMIT Offsets So Slow?

Impact of Higher LIMIT Offset on MySQL Query Speed

When querying a large MySQL table with an increasing LIMIT offset in conjunction with ORDER BY, users may encounter a notable slowdown in query speed. This is particularly evident when the table exceeds 16 million records or is approximately 2GB in size.

For instance, the following query with a small offset executes significantly faster than the one with a larger offset:

SELECT * FROM large ORDER BY `id` LIMIT 0, 30

SELECT * FROM large ORDER BY `id` LIMIT 10000, 30

Although both queries only retrieve 30 rows, the latter takes substantially longer despite the negligible overhead of ORDER BY.

To optimize this scenario, consider using an alternative approach:

1. Store the last ID of a fetched set of rows (e.g., lastId = 530).
2. Modify the query to include the condition WHERE id > lastId.

SELECT * FROM large WHERE `id` > lastId LIMIT 0, 30

By consistently maintaining a zero offset, the query will consistently perform at an optimal speed, even when fetching large amounts of data in iterations.

The above is the detailed content of Why Are MySQL Queries with Large LIMIT Offsets So Slow?. For more information, please follow other related articles on the PHP Chinese website!