About innodb count Optimization in mysql

Generally, secondary indexes are used for count:
For example, if id is pk aid, it is secondary index.

Use
Copy codeThe Code is as follows:
Select count (*) from table where id> = 0;
Or
Select count (*) from table;

The results are the same. pk indexes are used by default, and full table scanning is required. Although the first type of performance may be higher, there is no obvious difference.

However, if secondary index is used
Copy codeThe Code is as follows:
Select count (*) from table where aid> = 0;

It will be much faster.

Why is secondary index scan faster than primary key scan? This requires understanding the differences between the clustered index and secondary index of innodb.

The clustered index of innodb stores the primary key and row data together, while the secondary index stores the data separately, and then a pointer points to the primary key.
Therefore, it is faster to use secondary index to scan the count (*) Statistical table records.
Primary key is mainly used to scan indexes and return results records.