Yet another article comparing two database features. This time the different between the two storage engines MyISAM and InnoDB from MySQL will be compared, so you can choose which one you should choose for your project.
Some fast facts:
InnoDB should always be used if data integrity is a priority because of the support for relationship constraints and transactions and for projects which are very write intensive because the row-level locking doesn’t freeze concurrent inserts and updates.
MyISAM is more easy to design for beginners as you don’t have to worry about foreign relationships. As result of the more simpler structure of MyISAM it is in the end, specially for reads faster then InnoDB does.
If the integrity of your data is not importent and you need only very fast reads on a read-only database it can still be usefull to use the old MyISAM storage engine. For all other cases you should always use InnoDB.
December 3rd, 2012 at 10:28 am
Just to add to your comparison; InnoDB does support full text search in MySQL 5.6.4: http://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html and here is some more background: http://blogs.innodb.com/wp/2011/07/innodb-full-text-search-tutorial/
December 3rd, 2012 at 2:09 pm
Only InnoDB cares about your data. MyISAM doesn’t have checksums so a 1 can turn into 2B with a bit flip and you’ll never know.
December 3rd, 2012 at 6:00 pm
You might also note, MyISAM also supports GIS functions. I have a six part review of these at
December 4th, 2012 at 5:21 am
This is a handy comparison, thanks for posting. Just to open the door a little, there are some other options as well. TokuDB, another alternative storage engine for MySQL and MariaDB, offers additional advantages for performance for larger databases, online schema changes, and higher compression. For a comparison of InnoDB and TokuDB, see http://bit.ly/uqnoyr
December 4th, 2012 at 8:07 am
This comparison is a bit trite. For instance, “MyISAM is more easy to design for beginners as you don’t have to worry about foreign relationships.” – there’s nothing inherently in innodb (or other engines) that requires “foreign relationships”. There are certainly many MySQL+InnoDB applications that don’t pay any special attention to relationships at all.
The follow-up statement is also a platitude – “As result of the more simpler structure of MyISAM it is in the end, specially for reads faster then InnoDB does.” There are many, many read workloads that will be much faster in InnoDB vs. MyISAM, and I don’t think a generic statement like “MyISAM is faster for reads” is particularly helpful without some context.
December 4th, 2012 at 1:55 pm
Also FK in InnoDB are a BAD idea. The SE interface doesn’t actually support FK so they are a hack. FK cascaded deletes don’t fire triggers for example.