About the InnoDB count optimization problem in Mysql sharing _mysql

Generally use the level two index to count:
For example: ID is PK aid is secondary index

Using

Copy Code code as follows:

Select COUNT (*) from table where ID >=0;
Or
Select COUNT (*) from table;

The effect is the same, are the default use of PK index, and all want to scan the whole table, although the first performance may be higher, but there is no significant difference.

But if you use secondary index

Copy Code code as follows:

Select COUNT (*) from table where aid>=0;

It will be much quicker.

Why is it faster to use secondary index scans than primary key scans? This requires understanding the difference between the clustered index and the secondary index of InnoDB.

InnoDB's clustered index is the primary key and row data are stored together, and secondary index is stored separately, and then there is a pointer to the primary key.
As a result, the number of count (*) tab records is required to be scanned with secondary index, apparently faster.
While the primary key is mainly in the scan index, but also to return the result record when the role is greater.