This is always mentioned that InnoDB is slower in giving results for COUNT(*) as compared to MyISAM. But as Peter points out in his blog that this fact only applies to COUNT(*) queries without WHERE clause. This text is from Peter's blog only - "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." Let's see what EXPLAIN has in store for us.
The explain states that the counting is going to be done on PRIMARY index and Using Index. The best part is that since it is going to use PRIMARY index and since it is NOT NULL, MySQL will actually count the values from the index itself. So, contrary to the thought that something like COUNT(1) will work faster is not true in this case. Here is an interesting case from a bug.
Using a secondary index is faster. But why? Generally speaking, the PRIMARY index should be faster because it is usually in order and can be read with sequential I/O at around 15 times more speed than generally fragmented secondary index. Actually this is a special case, since the secondary index is inserted into the table in perfect order, which is very rare. Also, as Heikki points out in the bug"Since the minimum record size of InnoDB is about 20 bytes, and the fill-factor of a secondary index is typically 70 %, we can calculate that if the row length is > 15 * 1.5 * 20 = 450 bytes, then scanning the secondary index would probably be a better option." Till this feature is implemented and we have a much better optimized count(*) for InnoDB, please use secondary index explicitly for counting rows if you satisfy any of above conditions. Hope this blog was helpful to you. Keep posting your comments.