title: How to optimize COUNT(*) performance on InnoDB by using index
body: <p>I have a largish but narrow InnoDB table with ~9m records. Doing <code>count(*)</code> or <code>count(id)</code> on the table is extremely slow (6+ seconds): </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>While the statement is not run too often it would be nice to optimize it. According to <a href="http://www.cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/" rel="noreferrer">http://www.cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/</a> this should be possible by forcing InnoDB to use an index:</p>
  
  <pre><code>SELECT COUNT(id) FROM perf2 USE INDEX (PRIMARY);
  </code></pre>
  
  <p>The explain plan seems fine:</p>
  
  <pre><code>id  select_type table   type    possible_keys   key     key_len ref     rows    Extra
  1   SIMPLE      perf2   index   NULL            PRIMARY 4       NULL    8906459 Using index
  </code></pre>
  
  <p>Unfortunately the statement is as slow as before. According to <a href="https://stackoverflow.com/questions/511820/select-count-is-slow-even-with-where-clause">&quot;SELECT COUNT(*)&quot; is slow, even with where clause</a> I've also tried optimizing the table without success.</p>
  
  <p>What/is the/re a way to optimize <code>COUNT(*)</code> performance on InnoDB?</p>