Precautions for MySQL conditional query and paging query under millions of data

Continuing from the previous section "Millions of data mysql paging problem", we add the query conditions:

select id from news 
where cate = 1
order by id desc 
limit 500000 ,10 
查询时间 20 秒

What a terrifying speed! ! Use the knowledge from the first section "Millions of Data MySQL Data Test Environment Introduction" to optimize:

select * from news
where cate = 1 and id > (select id from news where cate = 1 order by id desc limit 500000,1 ) 
order by id desc 
limit 0,10 
查询时间 15 秒

The optimization effect is not obvious, but the impact of the conditions is still great! In this case, no matter how we optimize the SQL statement, we cannot solve the problem of operating efficiency. So let's change the idea: create an index table to record only the ID and classification information of the article. We divide the large field of article content into separate parts.

表 news2 [ 文章表 引擎 myisam 字符集 utf-8 ]
-------------------------------------------------
idint11主键自动增加
cateint11索引

Synchronize the two tables when writing data. If you query, you can use news2 to perform conditional query:

select * from news
where cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000,1 ) 
order by id desc 
limit 0,10

Note that the condition id > is used later in the news2 table!

The running time is 1.23 seconds, we can see that the running time is reduced by nearly 20 times! ! When the data is around 100,000, the query time can be kept at around 0.5 seconds, which is a value gradually approaching what we can tolerate!

But 1 second is still an unacceptable value for the server! ! Is there anything else that can be optimized? ? We tried a great change:

Changed the storage engine of news2 to innodb, and the execution results were amazing!

select * from news
where cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000,1 ) 
order by id desc 
limit 0,10

Only takes 0.2 seconds, very good speed. Why is there such a big difference? Please watch the next article for a detailed explanation of the mysql storage engine.

The above is the detailed content of Precautions for MySQL conditional query and paging query under millions of data. For more information, please follow other related articles on the PHP Chinese website!