The InnoDB engine is different in statistics and MyISAM, and MyISAM has a built-in counter, so when you use the Select COUNT (*) from table, you can directly retrieve data from the counter. And InnoDB must be scanned all the time to get the total number. To solve this problem in a preliminary way, we need to do some work different from MyISAM:
1, use the second index (generally not using primary key index), and add where conditions, such as:
Copy Code code as follows:
Select COUNT (*) from the product where comp_id>=0;
Show index from product;
ID PRIMARY Key
COMP_ID Index
2, if only need to rough statistics can also be used
Show status from product; To get the approximate value
This method can be used in data paging!
3, the use of external counters, such as the establishment of a trigger to count or on the program to use the cache time to count, the flaw is that these methods will consume some additional resources!
Resources:
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 # One about MyISAM to Innodb migration are warning what INNODB is very slow in COUNT (*) queries. The part which I often however-omitted is fact it only applies to COUNT (*) queries without WHERE clause.
So if you have query like SELECT COUNT (*) from USER It'll be much faster for MyISAM (MEMORY and some others) tables Beca Use they would simply read number of rows in the table from stored value. Innodb'll however need to perform full table scan or full index scan because it does not have such counter, it also can ' T is solved by simple singe counter to Innodb tables as different transactions may | Different number of rows in the TA ble.
If you have query like SELECT COUNT (*) from IMAGE WHERE user_id=5 This query would be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can is faster or slower both for MyISAM and Innodb depending on various conditions.
In the real applications there are much more queries of second type rather than the it is typically not as bad probl EM as it may look. Most typically count of rows are needed by admin tools which may show it in table statistics, it may also to used in Applic ation stats to show something like "We have 123.345 users which have uploaded 1.344.656 images" But these are normally EAS Y to remove.
So remember Innodb are not slow to all COUNT (*) queries but only to very specific case of COUNT (*) query without WHERE CL Ause. It does not mean I would don't like to the it fixed though, it is pretty annoying.
Turn from: http://www.sphinxsearch.org/archives/89
