Sharing of SQL optimization experience when offset is too large during MySQL paging

Find the problem

When we display the contents in a list, we will inevitably encounter paging problems, because the number of contents in the list may be large, but the size of the interface that the user can see at one time is limited. It is impossible to display all the contents in one interface. Fetching too much data from the backend at one time will also cause additional pressure on the backend.

Usually the following statement is used for paging query:

SELECT
*
FROM table
where condition1 = 0
and condition2 = 0
and condition3 = -1
and condition4 = -1
order by id asc
LIMIT 2000 OFFSET 50000

When the offset is particularly large, the execution efficiency of this statement will be significantly reduced, and the efficiency decreases as the offset increases.

The reasons are:

MySQL does not skip the offset row, but takes the offset+N rows, then returns the previous offset row and returns N rows. When the offset is particularly large and the single data is also large, the more data needs to be obtained each time the query is made, the slower it will be.

Optimization plan:

SELECT
*
FROM table
JOIN
(select id from table
where condition1 = 0
and condition2 = 0
and condition3 = -1
and condition4 = -1
order by id asc
LIMIT 2000 OFFSET 50000)
as tmp using(id)

or

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

First obtain the primary key list, and then query the target data by the primary key. Even if the offset is large, many primary keys are obtained instead of all field data. Relatively speaking, the efficiency will be greatly improved.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.