EXPLAIN - Insights: Large Offset

Description:

When a query uses a LIMIT, it fetches data from the underlying table until the desired number of rows is reached. This is pretty efficient (if you need the top N rows according to some specific ordering, the data may need to be sorted first and that can be expensive, but the LIMIT itself is cheap).

However, when adding an OFFSET, there is no easy way to skip past those rows: they must be read from disk and discarded. A query with LIMIT 10 OFFSET 1000 will need to read 1010 rows from disk, and throw away 1000 of them. As the offset grows larger and larger, this becomes more and more inefficient.

Recommended Action:

If using offsets for pagination, consider a more efficient pagination strategy, e.g. keyset-based pagination.

Learn More:

• Use The Index, Luke! - OFFSET is bad for skipping previos rows