01 Jan

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.

Leave a Reply

-- Kedar Vaijanapurkar --