Shulou(Shulou.com)06/01 Report--

The following mainly bring you a large amount of data mysql paging query brief analysis, hope that a large amount of data mysql paging query brief analysis can bring you practical use, which is also my main purpose of editing this article. All right, don't talk too much nonsense, let's just read the following.

We often use paging in the development process, and the core technology is to use limit to read data. During the paging test using limit, you get the following data:

Select * from news order by id desc limit 10 takes 0.003 seconds select * from news order by id desc limit 10000 10 takes 0.058 seconds select * from news order by id desc limit 1000000 10 takes 0.575 seconds select * from news order by id desc limit 1000000 10 takes 7.28s

We are surprised to find that in the case of a large amount of data, the larger the starting point of mysql, the slower the query speed, and the query speed of 1 million entries has already taken 7 seconds. This is a number that we cannot accept!

Improvement plan 1

Select * from news where id > (select id from news order by id desc limit 1000000, 1) order by id desc limit 0Jing 10

Query time 0.365 seconds, the improvement of efficiency is very obvious! What is the principle?

We use criteria to filter id, and in the subquery (select id from news order by id desc limit 1000000, 1) we only query id, which saves a lot of query overhead compared to select * or select multiple fields!

Improvement plan 2

Suitable for id continuous system, the speed is extremely fast!

Select * from news where id between 1000000 and 1000010 order by id desc

Not suitable for conditional id discontiguous queries. Very fast!

For the above about the large amount of data mysql paging query brief analysis, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.