During the development process, we often use paging. The core technology is to use limit to read data. During the test process of using limit for paging, the following data was obtained:
select * from news order by id desc limit 0,10 耗时0.003秒 select * from news order by id desc limit 10000,10 耗时0.058秒 select * from news order by id desc limit 100000,10 耗时0.575秒 select * from news order by id desc limit 1000000,10 耗时7.28秒
We were surprised to find that the larger the paging starting point of MySQL is when the data volume is large, the slower the query speed. The query speed for 1 million items has already been It takes 7 seconds. This is a value 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 0,10
The query time is 0.365 seconds, and the efficiency improvement is very obvious! ! What is the principle? ? ?
We used conditions to filter the id. In the subquery (select id from news order by id desc limit 1000000, 1), we only queried the id field. Compared with select * or select multiple fields Save a lot of query overhead!
Improvement Plan 2
Suitable for systems with continuous IDs, extremely fast!
select * from news where id between 1000000 and 1000010 order by id desc
Not suitable for queries with conditions and discontinuous IDs. high speed!
The above is the detailed content of Mysql paging problem under millions of data. For more information, please follow other related articles on the PHP Chinese website!