MyISAM was the default storage engine chosen by MySQL database, when creating a new table. But since 5.5 version, InnoDB is the default storage engine.
The major differences between these two storage engines are :
- InnoDB supports transactions which is not supported by tables which use MyISAM storage engine. InnoDB table supports foreign keys, commit, rollback, roll-and forward operations
- InnoDB has row-level locking, relational integrity i.e. supports foreign keys, which is not possible in MyISAM.
- InnoDB ‘s performance for high volume data cannot be beaten by any other storage engines available.
- The size of MyISAM table can be up to 256TB, which is huge. InnoDB tables can be upto 64 TB
- In addition, MyISAM tables can be compressed into read-only tables to save space.
Tables created in MyISAM are known to have higher speed compared to tables in InnoDB. But since InnoDB supports volume, transactions, integrity it’s always a better option which you are dealing with a larger database. It is worth mentioning that a single database can have tables of different storage engines.
MyISAM is good for read-heavy applications, but it doesn’t scale very well when there are a lot of writes. Even if you are updating one field of one row, the whole table gets locked, and no other process can even read from it until that query is finished. MyISAM is very fast at calculating SELECT COUNT(*) types of queries.
InnoDB tends to be a more complicated storage engine and can be slower than MyISAM for most small applications. But it supports row-based locking, which scales better. It also supports some more advanced features such as transactions.