Choosing between MyISAM and INNODB – MySQL Storage Engines

Choosing between MyISAM and INNODB – MySQL Storage Engines

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.