Experience on SQL optimization with excessive offset during mysql paging, mysqloffset

Experience on SQL optimization with excessive offset during mysql paging, mysqloffset

Problems Found

When we display the content in a list, we will inevitably encounter paging problems, because the number of content in the list may be large, but the page size that the user can see at a time is limited, it is impossible for a single interface to display all the content. Retrieving too much data from the backend at a time also puts extra pressure on the backend.

This statement is usually used for paging query:

SELECT*FROM tablewhere condition1 = 0and condition2 = 0and condition3 = -1and condition4 = -1order by id ascLIMIT 2000 OFFSET 50000

When the offset is very large, the execution efficiency of this statement will be significantly reduced, and the efficiency will decrease with the increase of offset.

The reason is:

MySQL does not skip the offset row, but takes the offset + N rows, and then returns the first offset row, and returns N rows. When the offset is particularly large and then the size of a single row is large, the more data you need to obtain each query, the slower it will naturally be.

Optimization solution:

SELECT*FROM tableJOIN(select id from tablewhere condition1 = 0and condition2 = 0and condition3 = -1and condition4 = -1order by id ascLIMIT 2000 OFFSET 50000)as tmp using(id)

Or

SELECT a.* FROM table a, (select id from tablewhere condition1 = 0and condition2 = 0and condition3 = -1and condition4 = -1order by id ascLIMIT 2000 OFFSET 50000) b where a.id = b.id

First, the primary key list is obtained, and then the target data is queried through the primary key. Even if the offset is large, many primary keys are obtained, instead of all field data, which improves the efficiency.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.