After reading at a lot of places for the the single repeatative question, “What engine shall I choose – MyISAM or Innodb?”, this is what I’ve got.
Following are points of consideration for MyISAM – MySQL storage engine:
- Tables are really fast for select-heavy loads
- Table level locks limit their scalability for write intensive multi-user environments.
- Smallest disk space consumption
- Fulltext index
- Merged and compressed tables.
Following are points of consideration for InnoDB – MySQL storage engine:
- ACID transactions
- Row level locking
- Consistent reads – allows you to reach excellent read write concurrency.
- Primary key clustering – gives excellent performance in some cases.
- Foreign key support.
- Both index and data pages can be cached.
- Automatic crash recovery – in case MySQL shutdown was unclean InnoDB tables will still recover to the consistent state- No check / repair like MyISAM may require.
- All updates have to pass through transactional engine in InnoDB, which often decreases performance compared to non-transactional storage engines.
Also consider:
- Choose MyISAM for large constant tables or logging tables, relatively infrequent updates or Fast selects – these will not lock the table for the long time and thus it will not reduce performance.
- Choose MyISAM if you really don’t need InnoDB.
- Choose InnoDB storage engine when following is required:
- Intensively updated tables – which can have many long selects running at the same time.
- Multi-statement transactions Advanced isolation levels and row-level locking Foreign key constraints. Well we can say for regular usage: MyISAM is for speed and InnoDB for data integrity.