Home > Database > Mysql Tutorial > body text

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

小云云
Release: 2017-12-25 11:16:37
Original
2073 people have browsed it

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
Copy after login

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)
Copy after login

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
Copy after login
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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!