6

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 ?
Improve this question
5
  • Besides Fulltext search, the other major lack of InnoDB is spatial indixes.
    – ypercubeᵀᴹ
    May 2, 2012 at 21:45
  • With Full-Text capability in 5.6, it is hard to find a compelling technical reason for not using innoDB. There may be licensing difference though.
    – srini.venigalla
    Jul 5, 2012 at 20:25
  • I'm kind of in the same boat. Wondering when to use what engine. May I ask, in general, in what context one engine is better suited than another? E.g. For storing data that almost never changes. Like preset status values (0 = disabled, 1 = avalible, 2 = paused etc.)... For storing only intergers, like a relation table Storing large texts for articles or blogging...
    – ThomasK
    Jul 5, 2012 at 20:26
  • the Aria Engine is getting close to use Transaction, so why don't use Aria instead?
    – jcho360
    Jul 5, 2012 at 20:28
  • that's up to you, depending for what will you use the table, and if you are using mysql V-5.6+ you can create text index in Innodb Engines
    – jcho360
    Jul 5, 2012 at 20:29

1 Answer 1

Reset to default
6

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.

Improve this answer
2
  • 1
    I agree with Rolando, especially the last two points regarding performance, if you are able to cache a large portion (preferably all) within RAM, the innodb option wins hands down. MyISAM data is cacheable, but only by the OS, not by MySQL, so you never know when the OS disk cache will get flushed out!
    – Dave Rix
    May 3, 2012 at 12:04
  • MySQL is NOT fully ACID compliant: Currently, cascaded foreign key actions do not activate triggers. This is also true for foreign key constraints, they can fail. But if you want to use MySQL, innoDB is imho your best option.
    – Frank Heikens
    Jul 5, 2012 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.