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

This article mainly gives you a brief introduction to mysql conditions and paging query notes. You can look up relevant professional terms on the Internet or find some related books to supplement them, so we will not dabble here. Let's go straight to the mysql conditions and paging query to pay attention to the topic of brief analysis. I hope it can bring you some practical help.

Select id from news where cate = 1order by id desc limit 500000, 10 query time 20 seconds

What a terrible speed! Make use of the knowledge of the first section "introduction to million data mysql data Test Environment" to optimize:

Select * from newswhere cate = 1 and id > (select id from newswhere cate = 1 order by id desc limit 500000Pol 1) order by id desc limit 0Jing 10 query time is 15 seconds

The optimization effect is not obvious, the influence of the condition is still very big! In this case, no matter how much we optimize the sql statement, we can't solve the problem of running efficiency. Then change the way of thinking: set up an index table, only record the id and classification information of the article, and we will separate the large field of the article content.

Table news2 [article table engine myisam character set utf-8]-idint11 primary key automatically adds cateint11 index

Synchronize the two tables when writing data, and news2 can be used for conditional queries:

Select * from newswhere cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000 Pione1) order by id desc limit 0Jing 10

Note that the news2 table is used after the condition id >!

The running time is 1.23 seconds, and we can see that the running time has been reduced by nearly 20 times! Data in 100000 or so is the query time can be maintained at about 0.5 seconds, is a gradually close to the value we can tolerate!

But 1 second is still an unacceptable value for the server! Is there any other way to optimize it? We tried a great change:

Change the storage engine of news2 to innodb, and the execution result is amazing!

Select * from newswhere cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000 Pione1) order by id desc limit 0Jing 10

It only takes 0.2 seconds, a great speed. Why is there such a big difference? Please check out the next mysql Storage engine detail article.

Mysql conditions and paging query attention brief analysis will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.