About the solution of mysql innodb count of * slow speed

The innodb engine is different from myisam in terms of statistics. Myisam has a counter built in, so when using select count(*) from table, the data can be extracted directly from the counter. And innodb has to scan the whole table to get the total number. To solve this problem initially, we need to do something different from myisam:

1. Use the second index (1 generally does not use the primary key index) and add where conditions, such as:

 
select count ( * )  from product where comp_id>=0 ; 
show index from product ; 
id primary key 
comp_id index 

2. It can also be used if only rough statistics are needed

show status from product; To get an approximate value
This method can be used in data paging!

3, using external counters, such as creating a trigger to count or using a cache method to time the count on the program, the drawback is that these methods will consume an additional 1 resource!

Reference:

mysql high-performance: http: / / www mysqlperformanceblog. com / 2006/12/01 / count - for - innodb - tables /
mysql DBA:http://imysql.cn/2008_06_24_speedup_innodb_count

COUNT(*) for Innodb Tables

I guess note number one about MyISAM to Innodb migration is warning what Innodb is very slow in COUNT(*) queries. The part which I often however see omitted is fact it only applies to COUNT(*) queries without WHERE clause.
So if you have query like SELECT COUNT(*) FROM USER It will be much faster for MyISAM (MEMORY and some others) tables because they would simply read number of rows in the table from stored value. Innodb will however need to perform full table scan or full index scan because it does not have such counter, it also can't be solved by simple singe counter for Innodb tables as different transactions may see different number of rows in the table.
If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on various conditions.
In real applications there are are queries of second type type so so typically count of rows is needed admin tools which may show in table statistics, it may also be in application to something like "We have 123.345 users which uploaded 1.344.656 images" but these are normally easy to remove.
So remember Innodb is not slow for ALL COUNT(*) queries but only for very specific case of COUNT(*) query without WHERE clause. It does not mean I would not like to see it fixed though, it is pretty annoying.

From: http: / / www sphinxsearch. org/archives / 89