Using LIMIT and OFFSET for deep paging results in performance degradation because the database needs to be scanned and skipped a large number of records. 1. Use cursor-based paging to obtain the next page data by remembering the sorting field (such as ID or timestamp) of the last record of the previous page to avoid scanning all previous rows; 2. Ensure that the sorting field has indexes, such as single or combined indexes, to speed up positioning records; 3. Constrain business restrictions on deep paging, such as setting the maximum page number, guiding users to filter, or asynchronously loading cache results. These methods can effectively improve the performance of paging query, especially in large data scenarios, cursor paging combined with index optimization is the most recommended method.

When you are using LIMIT and OFFSET for paging queries, especially when OFFSET is very large, performance tends to drop sharply. The database needs to scan and skip the previous large number of records to get the part of the data you really want. This problem is particularly evident when implementing deep paging (such as after page 1000).

To optimize LIMIT queries with large offsets, the key is to reduce unnecessary scans and index traversals.

1. Use cursor-based pagination instead of OFFSET pagination

The traditional LIMIT offset, size method is inefficient when the offset is large, because it requires scanning all previous rows. A more efficient alternative is Cursor-based Pagination .

• Principle: Instead of remembering which page is currently on, remembering the sorting field (such as ID, timestamp, etc.) of the last record on the previous page, and then the next page starts from that location.
• Sample SQL:

   SELECT id, name FROM users WHERE id > 12345 ORDER BY id ASC LIMIT 20;

• Advantages: It won't slow down as the number of pages increases, because only the data you need is always scanned.
• Disadvantages: It cannot jump to a certain page directly, and it is not suitable for frequent insertion/deletion.

Note: If you are using timestamps as cursors, you should pay attention to the problem of repetition of time. It is best to use it with unique fields (such as primary keys).

2. Add index to the sorted field

Whether it is traditional paging or cursor paging, it is the basic premise to ensure that there is a suitable index on the sorting field .

• If you sort by created_at , index it.
• If you often combine the order (such as by state first and by time), consider combining indexes.

Indexing allows the database to quickly locate the location you want, rather than scanning the full table.

Common practices:

• Single field index: CREATE INDEX idx_created_at ON table(created_at);
• Multi-field index: CREATE INDEX idx_status_created ON table(status, created_at);

In this way, even if you use OFFSET , you can try to use the index to speed up skipping previous records.

3. Limit or downgrade the "deep pagination"

In some scenarios, users do not actually turn to hundreds of pages, but the system still supports it. You can consider this at this time:

• Limit the maximum page number : For example, only page 100 is allowed to be turned to at most, and the prompt is "Please adjust the filter conditions" after it exceeds it.
• Asynchronous loading cache : For background analysis pages, the results can be cached or generated asynchronously.
• Full-text search or filtering alternative paging : guides users to narrow the scope through search and filter, rather than relying on page turning to find content.

Although these strategies do not solve technical problems, they can effectively alleviate the performance pressure in actual use.

Basically these are the methods.
Cursor paging is the most recommended approach, especially when the data volume is large; indexing is the basic guarantee; restrictions on business operations of deep paging are also a practical idea.
The key to optimizing such problems is not to bypass paging, but to understand how users use and how data can be used, and then choose the right strategy.

The above is the detailed content of How to optimize LIMIT with a large offset for pagination?. For more information, please follow other related articles on the PHP Chinese website!