mysql innodb count 优化,如何通过使用索引在InnoDB上优化COUNT（*）性能

I have a largish but narrow InnoDB table with ~9m records. Doing count(*) or count(id) on the table is extremely slow (6+ seconds):

DROP TABLE IF EXISTS `perf2`;

CREATE TABLE `perf2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`channel_id` int(11) DEFAULT NULL,

`timestamp` bigint(20) NOT NULL,

`value` double NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `ts_uniq` (`channel_id`,`timestamp`),

KEY `IDX_CHANNEL_ID` (`channel_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

RESET QUERY CACHE;

SELECT COUNT(*) FROM perf2;

While the statement is not run too often it would be nice to optimize it. According to http://www.cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/ this should be possible by forcing InnoDB to use an index:

SELECT COUNT(id) FROM perf2 USE INDEX (PRIMARY);

The explain plan seems fine:

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE perf2 index NULL PRIMARY 4 NULL 8906459 Using index

Unfortunately the statement is as slow as before. According to "SELECT COUNT(*)" is slow, even with where clause I've also tried optimizing the table without success.

What/is the/re a way to optimize COUNT(*) performance on InnoDB?

解决方案

For the time being I've solved the problem by using this approximation:

EXPLAIN SELECT COUNT(id) FROM data USE INDEX (PRIMARY)

The approximate number of rows can be read from the rows column of the explain plan when using InnoDB as shown above. When using MyISAM this will remain EMPTY as the table reference isbeing optimized away- so if empty fallback to traditional SELECT COUNT instead.