Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?
share|improve this question
    
Besides Fulltext search, the other major lack of InnoDB is spatial indixes. –  ypercube May 2 '12 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 '12 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 '12 at 20:26
    
the Aria Engine is getting close to use Transaction, so why don't use Aria instead? –  jcho360 Jul 5 '12 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 '12 at 20:29
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
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 '12 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 '12 at 16:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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