MyISAM and InnoDB compared

By | December 3, 2012

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 supports foreign keys
  • InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock!
  • InnoDB supports transactions
  • MyISAM has full-text search index which InnoDB doesn’t have

InnoDB

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

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.

 

6 thoughts on “MyISAM and InnoDB compared

  1. Justin Swanhart

    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.

    Reply
  2. Lawrence

    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

    Reply
  3. Andrew

    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.

    Reply
    1. Justin Swanhart

      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.

      Reply

Leave a Reply