Count and overwrite indexes in the MySQL Optimizer

The problem of Count and index coverage in the MySQL optimizer indicates that an optimizer still needs to be improved. Based on the where condition and the fields in select_list, the optimizer determines whether to return to the clustered index to retrieve data after an index (sta) is used. The basic practice is to determine all the fields in select_list and where after an index is determined. If the fields exist in the sta index, overwrite indexes can be used. The first explan can use the overwrite Index (Using Index) because the select_list contains only count (*), while count (*) is specially processed in the Syntax Parsing stage and is not used as a special field. In the current implementation of the second field, because gmt is not part of the sta index (the sta index defines only one field, and the clustered index structure is sta and auci ). Therefore, it is determined that the index cannot be overwritten. The time difference between the two queries is 25 times (0.19 s vs 5S) after W of data is placed in the performance difference table ). In fact, there is an improvement. I say this is "unscientific" because the second statement cannot overwrite the index. According to the meaning of count (gmt), the number of all rows whose gmt is not NULL is calculated. However, in the table definition, the gmt is not null. Then it can be converted to count! Currently, the processing methods of count, sum, and count (distinct) operations are not separated. The application is cautious. Normally, when calculating the total number, the maximum value is count (auci) or count (*). It does not intentionally count another field. If MyISAM is used at that time, you need to be careful. From the above analysis, we can infer that if this is a MyISAM table and the where condition is the same, count (*) can overwrite the index, but count (auci) won't work.