Re: COUNT(*) on innodb

[Jay Pipes]

Not necessarily, just wanted some more information. SELECT COUNT(*) FROM some_table is handled very differently in InnoDB versus MyISAM, which will return the result directly from the .MYI/.MYD header information. With InnoDB, it's more complicated, and has been the cause of a number of frustrations for forum web sites trying to move from MyISAM to InnoDB (forum software typically has a lot of SELECT COUNT(*) type queries). InnoDB still has this restriction, and the most common workaround is to keep a counter table which gets updated with the number of rows when you INSERT/DELETE from the table. You can read more here:

http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com