Shulou(Shulou.com)06/02 Report--

This article mainly explains "what are the reasons for not using offset and limit in paging". The content in the article is simple and clear, and it is easy to learn and understand. below, please follow the editor's way of thinking to study and learn "what are the reasons for not using offset and limit in paging"?

What's wrong with OFFSET and LIMIT?

As we briefly discussed in the previous paragraphs, OFFSET and LIMIT are ideal for projects that use little or no data.

The problem arises when your database begins to collect more data than the server can store in memory, and you still need to page the data with high performance.

To do this, the database needs to perform an inefficient full table scan every time a page is requested (insertions and deletions may occur during this time, and we don't want the data to be out of date!).

What is a full table scan? A full table scan (also known as a sequential scan) refers to scanning in the database, reading each record in the table sequentially, and then checking whether the conditions of the columns encountered are valid. This type of scan is considered the slowest because of the large amount of I / O read from disk, including multiple searches and expensive disk-to-memory transfers.

This means that if you have 100.000.000 users and your required OFFSET is 50.000.000, then it will need to take all these records (or even not!), put them in memory, and then get the 20 results specified in LIMIT.

Therefore, to display pages like this on the site:

50.000 to 50.020 of 100.000

First of all, you need to get 50.000 lines to see if this is inefficient.

What should you use?

This is what you should use:

This is cursor-based paging.

You should store the last received primary key (usually an ID) and Limit, rather than storing the current offset and limit locally and passing them along with each request, so that the query may end up similar.

Why? Because by explicitly passing the latest read rows, you can tell the database exactly where to start the search based on a valid index key, regardless of any rows outside that range.

Take the following comparison as an example:

For our optimized version:

The records received are exactly the same, but the first query took 12.80 seconds and the second query took 0.01 seconds. Can you feel the difference?

Matters needing attention

In order for cursor paging to work seamlessly, you need to have a unique, sequential column (or column), such as a unique integer ID, which can be a problem in certain cases.

As always, my advice is to be sure to consider the pros and cons of each table schema and what kind of query you need to execute in each table. If you need to process a large amount of relevant data in a query, Rick James's "Lists article" article may provide you with more in-depth guidance.

If the problem at hand is related to the absence of a primary key, for example, we have a many-to-many relational table, the traditional OFFSET/LIMIT approach can always be used in these cases, but this will reintroduce potentially slower queries. Therefore, I recommend using an automatically incrementing primary key in the table to be paged, even if only for paging purposes.

Thank you for your reading, the above is the content of "what is the reason for not using offset and limit in paging". After the study of this article, I believe you have a deeper understanding of what is the reason for not using offset and limit in paging, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.