Inside a table with 5 millions rows, a
SELECT count(*) FROM table could be instant in MyISAM but would take several seconds in InnoDB.
Why this way? Why haven't they optimise count in InnoDB like MyISAM?
It is a difference in implementation. InnoDB supports transactions and for that reason it needs to count the rows according to your transactionally consistent view on the table(s). Since MyISAM does not support Acidity qualities, if your row is placed, it's placed for everybody and for that reason it may just update a count it keeps inside the storage engine.
MyIsam has the amount of rows saved somewhere so the totally almost instant, InnoDB needs to scan the table to obtain the full count. For those who have a conditional count ( Ex: "Choose COUNT(*) WHERE CUSTOMER =4" ) both need to do the scan and there's very little difference within this situation