|
InnoDB |
MyISAM |
| InnoDB implements row-level locking. | MyISAM implements table-level locking. |
| Automatic Crash Recovery. | No Crash Recovery |
| InnoDB doesn’t have FULLTEXT search indexes. | MyISAM have FULLTEXT search indexes. |
| InnoDB also implements transactions, foreign keys and relationship constraints. | MyISAM does not support transactions, foreign keys and relationship constraints. |
| InnoDB stores both data and indexes in one file. | MyISAM stores indexes in one file and data in another. |
| InnoDB uses a buffer pool to cache both data and indexes. | MyISAM uses key buffers for caching indexes and leaves the data caching management to the operating system. |
In InnoDB the COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used) execute slower than in MyISAM because the row count is not stored internally.
Overall I would recommend InnoDB for most purposes and MyISAM for specialized uses only. InnoDB is now the default engine in new MySQL versions.
