mikebernat.com Web-Development, Design, Usability

Most people who use MySQL know that MyISAM and InnoDB are the two most-common database engines available with the popular open-source database provider. I would bet that most of those people don't even take the time to select a storage-engine and just accept the database default. Those of you who are left probably heard from a friend who saw something online that said one of the two is better than the other. Those of you who are left will still probably learn a thing or two here :)

Storage Engine

The storage-engine is what will store, handle, and retrieve information for a particular table. As hinted by the opening of this article, there is no be-all end-all solution to every or even most cases. Each has very specific pros and cons that by design can not be all-inclusive of each other.

This is a screenshot of some of the available storage engines given to you by phpMyAdmin when you create a new table.

MyISAM

Let's start with MyISAM since it is the default engine with MySQL. MyISAM is based on the older but proven ISAM code but has been extended to be fully-featured while retaining the reliability. Data in MyISAM tables is split between three different files on the disk. One for the table format, another for the data, and lastly a third for the indexes.

The maximum number of rows supported amounts to somewhere around ~4.295E+09 and can have up to 64 indexed fields per table. Both of these limits can be greatly increased by compiling a special version of MySQL.

Text/Blob fields are able to be fully-indexed which is of great importance to search functions.

Much more technical information can be found on MySQL's MyISAM Manual Page.

InnoDB

InnoDB is relatively newer so the scene than MyISAM is so people are still weary about its use in environments than run fine under MyISAM. InnoDB is transaction-safe meaning data-integrity is maintained throughout the entire query process. InnoDB also provides row-locking, as opposed to table-locking, meaning while one query is busy updating or inserting a row, another query can update a different row at the same time. These features increase multi-user concurrency and performance.

Another great feature InnoDB boasts is the ability to use foreign-key constraints. FK constraints allows developers to ensure that inserted data referencing another table remains valid. For example, if you had an authors table and a books table and you wanted to insert a new book while referencing the author. The author would have to exist in the authors table before inserting them in the books table because a foreign key was specified in the books table. At the same time you would not be able to delete an author from the authors table if they had any entries associated with them in the books table. More on this in a later article...

Because of its row-locking feature InnoDB is said to thrive in high load environments. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

Comparison

MyISAM in most cases will be faster than InnoDB for run of the mill sort of work. Selecting, updating and inserting are all very speedy under normal circumstances. It is the default engine chosen by the MySQL development team which speaks to its integrity, reliability, and performance.

InnoDB, or the OSX of the database-engine world, has emerged with some nifty features and created a niche for itself very quickly. Boasting features like row-level locking, transaction-safe queries, and relational table design are all very temping. The first two features really shine in a table that is constantly getting hammered like a logs, or search engine-type table. Since queries happen in the blink of an eye (faster actually) table-level locking(MyISAM) is sufficient in most other normal cases.

InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk.

Decision Matrix

There is no winner.

REMEMBER! It's OK to mix table types in the same database! In fact it's recommended and frequently required. However, it is important to note that if you are having performance issues when joining the two types, try converting one to the other and see if that fixes it. This issue does not happen often but it has been reported.

Extra Reading

MySQL Performance Blog - Graphically shows how InnoDB overtakes MyISAM at a certain number of queries per second.

MySQL Manual InnoDB Overview - Great overview of capabilities and limitations.

MySQL Manual MyISAM Overview - Same as above

MySQL Manual Storage Engines - Information on the other less-used storage engines MySQL offers.

Please share on your success/horror stories with each engine and how you dealt with them. I would be very interested to hear about them.