Why is Query Performance Degraded with Large LIMIT Offsets in MySQL?

Query Performance Degradation with Increasing LIMIT Offset

Databases often encounter performance bottlenecks when utilizing LIMIT with an offset, especially when dealing with large tables. This issue is particularly evident in MySQL when using the LIMIT OFFSET clause with high offset values and ORDER BY.

One such example is a table named "large" with over 16 million records, occupying approximately 2GB of storage space. Executing a query with a low offset value:

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

completes significantly faster compared to a similar query with a higher offset:

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

Although both queries retrieve the same number of records (30), the overhead of ORDER BY does not account for the performance difference.

Optimization Solution

To address this issue and improve performance, a different approach can be adopted. Instead of incrementing the offset in a loop to collect a large amount of data, we can hold the last ID of the previous data set and add a WHERE condition to the subsequent query:

1. Store the last ID of the previous data set, e.g., lastId = 530.
2. Reformat the query as follows:

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

By maintaining a zero offset and querying for records beyond the last known ID, the performance can be drastically improved.

The above is the detailed content of Why is Query Performance Degraded with Large LIMIT Offsets in MySQL?. For more information, please follow other related articles on the PHP Chinese website!