Mysql paging problem under millions of data

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!