Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to optimize the excessive offset Sql in mysql paging. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

A statement like this is usually used when paging queries:

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

When the offset is particularly large, the execution efficiency of this statement decreases significantly, and the efficiency decreases with the increase of offset.

The reasons are:

Instead of skipping the offset row, MySQL takes the offset+N row, then returns the abandoned previous offset row and returns N rows. When the offset is particularly large, and then a single piece of data is also very large, each query needs to get more data, which is naturally very slow.

Optimization scheme:

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 get the list of primary keys, and then query the target data through the primary key. Even if the offset is very large, you can get a lot of primary keys instead of all the field data. Relatively speaking, the efficiency will be greatly improved.

On "how to optimize mysql paging when the offset is too large Sql" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

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.