Why Are High LIMIT OFFSET Values in MySQL Queries So Slow?

Impact of Higher LIMIT OFFSET on MySQL Query Performance

When executing a SELECT query with a LIMIT clause and a large table, setting a high offset value can drastically slow down the query's execution time. This behavior is particularly noticeable when using the ORDER BY clause with the primary key.

For example, consider a table called "large" with over 16 million records (2GB in size). A query like the following:

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

can execute much faster compared to a query with a higher offset, such as:

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

Both queries retrieve the same number of records (30), indicating that the performance difference is not due to the overhead of the ORDER BY operation.

To optimize the query, it is recommended to minimize the use of high LIMIT offsets. Instead, consider using a WHERE clause to select records based on a known identifier, which will result in a zero offset.

For instance, if the goal is to retrieve the latest 30 rows, the following query can be used:

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

By maintaining a record of the last known ID (last_known_id), this approach allows the query to always start from an offset of zero, significantly improving performance.

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