Slow mysql innodb count (*)

The innodb engine is different from myisam in terms of statistics. Myisam has a built-in counter. Therefore, when using select count (*) from table, you can directly retrieve data from the counter. Innodb must scan the entire table once to obtain the total number. To solve this problem, you need to do something different from myisam:

1. Use the second index (generally, the primary key index is not used) and add the where condition, for example:

Copy codeThe Code is as follows:
Select count (*) from product where comp_id> = 0;
Show index from product;
Id primary key
Comp_id index


2. If you only need rough statistics, you can also use

Show status from product; to get the approximate value
This method can be used in data paging!

3. Use an external counter, such as creating a trigger to count or using the cache method on the program. The defect is that these methods consume additional resources!

References:

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 wowould 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 using Ming index rage scan. this can be faster or slower both for MyISAM and Innodb depending on various conditions.
In real applications there are much more queries of second type rather than first type so it is typically not as bad problem as it may look. most typically count of rows is needed by admin tools which may show it in table statistics, it may also be used in application stats to show something like "We have 123.345 users which have 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 wocould not like to see it fixed though, it is pretty annoying.