When do a select count(*), is the performance better on MyIsam table than InnoDB table?
I read on the web that MyISAM stores total number of rows in MYI file header. It's read into memory when table is opened.
InnoDB doesn't store total number of rows anywhere.
If we have InnoDB tables and also need to do a lot of count(*), is there anyway to improve the count(*) on InnoDB?
You can improve performance of row counting in 2 ways.
Store your own row counter that gets updated on INSERT/DELETE. This is entirely dependant on your database load as it creates overhead on every data altering operation. However, on the plus side, it makes row counting very fast. Another down side is that your queries are not immediately obvious as COUNT(*) FROM table.
If you do COUNT(field) on an indexed column that has low(ish) cardinality, this seems to give a significant boost to performance on some columns. I'm not sure why it doesn't work for all, perhaps soneone can enlighten me. However, a total table scan of COUNT(*) on one of my tables takes 25 seconds, if I choose the right indexed colum, COUNT(column) takes less than 2 seconds.