InnoDBorMyISAM

MyISAM is the default storage engine in MySQL, but many people forget there are other choices. Deciding which storage engine(s) to use can be tricky, but it’s worth evaluating whether or not MyISAM suits your needs. There are a number of storage engines available, but I will focus on MyISAM and InnoDB as these are used most often.

Some things to consider:

• Do you need foreign keys?
• Do you need transactions?
• Do you need full text indexes?
• What are your data access (query) patterns?
• How large is your data set?

Reviewing the above points will get you going in the right direction, but there are exceptions. If you need transactions or foreign keys, InnoDB is the way to go. For full text indexes your usual choice is MyISAM since it has built-in support; however, it doesn’t usually scale past a couple of million rows. It can take some time to properly configure, but you can obtain full text indexes for your InnoDB tables by using Sphinx.

The size of your data set can become a major factor in which engine you use. Larger data sets tend to favor InnoDB for its transactions and crash recovery. While the time to recover MyISAM scales with the size of the data set, InnoDB’s recovery time scales with the size of the transaction logs – which you have some control over. For example, you might endure a recovery time of hours or even days for MyISAM compared to a few minutes for InnoDB.

The manner in which you read from and write to your tables can have a big impact on performance with respect to the storage engine(s) you use. COUNT() is quick on MyISAM tables, but quite painful and best avoided with InnoDB tables. Primary key lookups are extremely quick in InnoDB, but beware of making your primary key too long as performance can suffer. Bulk inserts are faster in MyISAM, but updates can be much faster in InnoDB – especially as concurrency increases.

So which one should you choose? If you’re working on a small project, MyISAM may be just fine for you. Even larger environments use MyISAM with great success, but it varies. If you plan on having a very large data set, and need transactions or foreign key constraints, it’s worth looking into InnoDB straight away. Just remember that InnoDB tables have a larger memory and storage footprint compared to MyISAM so plan ahead. Converting 100s of GB of MyISAM tables to InnoDB could present a bad surprise.