Sql optimization example sharing when offset is too large during mysql paging

When we display the content in a list, we will inevitably encounter paging problems, because the amount of content in the list may be large, but the size of the interface that users can see at one time is limited, and it is impossible to display all the content on one interface. , fetching too much data from the backend at once will also cause additional pressure on the backend. Mysql paging is a function that we often encounter in development. Recently, we encountered a problem when implementing this function. Therefore, this article mainly introduces to you the SQL optimization experience of excessive offset when mysql paging. The article introduces very It is detailed and has certain reference and learning value for everyone's study or work. Friends who need it can follow the editor to take a look.

Usually such a statement is used when performing paging queries:

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 reason is:

MySQL does not skip the offset row, but takes the offset+N row, and then returns the offset row before giving up. Returning N rows, when the offset is particularly large and the single piece of data is also large, more data needs to be obtained for each query, which will naturally be very slow.

Optimization solution:

##

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 through the primary key. Even if the offset is large, a lot of primary keys are obtained instead of all field data. Relatively speaking, the efficiency will be improved a lot.

Related recommendations:

MySQL optimization summary-total number of queries

Summary of commonly used mysql optimization sql statement query methods

MySQL optimization includes three aspects

The above is the detailed content of Sql optimization example sharing when offset is too large during mysql paging. For more information, please follow other related articles on the PHP Chinese website!