MySQL Storage Engine – Which One to Choose?

full-text-searchinnodbmyisamMySQLstorage-engine

I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB).

The theories say:

  • Both work with BTree indexes, but MyISAM can work with FULLTEXT index also.
  • InnoDB is more strict in data integrity while MyISAM is loose.
  • InnoDB has transactions while MyISAM does not.
  • InnoDB has foreign keys and relationship contraints while MyISAM does not.
  • MyISAM are faster to read but slower to write (I'm not sure about this one).

When I create a table, should I always sacrifice something if I want to keep integrity and I want to speed up the query making search by "text"?

How do you decide which engine to use if you need

  • integrity ?
  • speed ?
  • constraints ?
  • search by text ?
  • transactions ?

Best Answer

Both works with b-tree index, but MyISAM can work with FULLTEXT index also.

MySQL 5.6 is supposed to support FULLTEXT indexing as well. Look for it when it goes GA.

InnoDB is more strict in data integrity, while MyISAM is loose

InnoDB has transactions while MyISAM does not.

InnoDB has foreign keys and relationship contraints while MyISAM does not.

This is true in light of the fact that InnoDB supports transaction isolation via MVCC. That, in part, makes InnoDB fully ACID compliant.

MyISAM are faster to read but slower to write (I'm not sure about this one)

This depends entirely on the row format of the MyISAM table (Fixed or Dynamic). Slow is relative. After all, each DML statement against a MyISAM performs a full table lock each and every time.

Given a large enough cache, InnoDB can be faster for both reads and writes because COMMITs can be delayed. This leaves data already cached in the InnoDB Buffer Pool available for reads right out of memory.

InnoDB caches both Data and Indexes while MyISAM only caches Indexes. Thus, disk reads for data from MyISAM is always required.

Related Question