The major
differences between these two storage engines are :
- InnoDB supports transactions which is not supported by tables which use MyISAM storage engine.
- InnoDB has row-level locking, relational integrity i.e. supports foreign keys, which is not possible in MyISAM. MyISAM can do only a table-level lock.
- InnoDB ‘s performance for high volume data cannot be beaten by any other storage engines available.
- InnoDB offers ACID transcations, row level locking, foreign key constraints, automatic crash recovery, table compression (read/write).
- MyISAM offers fast count(*), full text indexing, smaller disk footprint, very high table compression ( read only ).
- innodb is journaled, and can recover from crashes where myisam can't, much like NTFS vs FAT file systems.
- In MyISAM the main mechanism used is the key cache. It only caches index pages from .MYI files
- In InnoDB the main mechanism used is the InnoDB Buffer Pool. It caches data and index pages from InnoDB tables accessed.
