Regarding Mysql index query efficiency issues

Question

SELECT COUNT(*) FROM `set_gif` WHERE `zhuanid` = 0 AND `webid` IN ('0','2','21','22','27','11','31') AND `empty` = 0 LIMIT 1 [ RunTime:0.1423s ]
SELECT `id`,`webid`,`catid`,`path`,`pname`,`pathall`,`title`,`cdn`,`sort`,`likecount`,`fsize`,`time`,`viewcount`,`likecount` FROM `set_gif` WHERE `zhuanid` = 0 AND `webid` IN ('0','2','21','22','27','11','31') AND `empty` = 0 ORDER BY time desc,id desc LIMIT 0,10 [ RunTime:0.0045s ]

Two sql statements, the latter runtime means execution time, the data volume is 150W

Field description zhuanid is a numerical value webid is a numerical value empty is 0 or 1

The common index used in the first sentence of SQL is a set of indexes of zhuanid webid empty numbers
The index used in the second sentence of SQL is a set of indexes of zhuanid webid empty. Time and id are a set of indexes

Why does the count in the first sentence take so long? Compared with the complex query in the second sentence, it takes a very short time

Answer 1

The first sentence is sql, limit 1
The second sentence is sql, limit 0, 10
You asked count to count 1.5 million items.... Do you want to be faster than checking the first 10 items?

Answer 2

My guess: The index field is not set to be non-nullable, causing count(*) to not use the index.

In addition, these two statements themselves are not equivalent. The first one is for the entire table, but the second one is not. There seems to be no comparison

Answer 3

You need to take a look at your execution plan. If the first SQL result set is large, you need to scan all the records that meet the conditions. In this case, the second SQL uses the time id index and only needs to find 10 records that meet the conditions. That’s it, so it will be faster. In addition, the combined index of zhuanid webid empty will only use the zhuanid column and the range part of webid. You can modify the index order to zhuanid empty webid

Answer 4

After the first SQL is executed, the result is saved in Cache.

The execution of the second SQL relies on the Cache of the first SQL, so it will be faster (in fact, the second SQL uses sorting, so it should be slower).

The poster can execute it after the first SQL is executed

RESET QUERY CACHE;

Reset the Cache and then execute the second SQL, the result is different.