Optimization of COUNT (*) by InnoDB under MySQL 5.7
In MySQL 5.7COUNT (), the total number of tables can be quickly obtained by selecting a clustered index and making an internal handler function call.
The execution plan shows: Extra:Select tables optimized away
In previous versions, the COUNT () request would normally be: scan the normal index to get this total.
6. The execution plan shows: Extra:Using index
If the clustered index is large (or the table has a large amount of data) and is not fully loaded into the buffer pool, the query mode of MySQL 5.7may be slower instead of using the original way (MySQL 5.6)
Solution: force the general index, but you still need to add WHERE conditions, otherwise it will not work.
Select count () from stock force index (idx_stock) where id > 0
Extra:using where,using index
