It’s not breaking news that MySQL is by far the most popular open-source RDBMS that exists today. The widespread adoption of the database server stems from a balance of robustness and solid performance; it doesn’t hurt to that it also supports a variety of storage engines, which gives users the ability to pick the one that best suits their needs.
Unfortunately, not everyone using MySQL is an experienced DBA with a deep background on each storage engine supported by the server. This is especially evident in the terrain of web development, where (in many cases) the design of a project’s database schema is conducted by taking into account only the kind of data that will be persisted between HTTP requests.
The bright side is that in most use cases, selecting the appropriate storage engine for a particular project can be much simpler than one might think. If you’ve been working with MySQL on a frequent basis and have followed its evolution over time, you probably know that the whole selection process can be reduced to a few typical options: the older MyISAM system and the newer, flashier InnoDB.
That being said, in this tutorial I’ll be conducting a comparison between the main characteristics offered by MyISAM and InnoDB, in this way making it easier for you to spot their differences without having to get lost in the overwhelming contents of the MySQL manual.
Being the storage engine included by default with MySQL from its early days, MyISAM (short for My Indexed Sequential Access Method) is a relatively simple storage mechanism (especially when compared to more complex and sophisticated implementations, such as InnoDB and DB2) which has a fast performance, due to the fact that each record is organized first into a sequential file, and then accessed via a set of small indexes or pointers annexed to the record in question.
It’s worth noting, however, that this simplicity and great performance comes with a hidden cost: first and foremost, MyISAM doesn’t provide support for transactional ACID operations, meaning that it’s not possible to run batches of operations in one go, or even to roll them back when something goes wrong. For obvious reasons, this can lead to potential inconsistencies in tables and affect the integrity of the stored data, thus making it necessary to tackle this and other related issues at application level.
In addition, when accessing a database, MyISAM uses an approach known as table-level blocking. Simply put, this implies that each time the server attempts to run an operation in the target table, it will be blocked during the execution process. While at first glance this operational mode seems to be quite efficient, it can quickly become a poor strategy, particularly when it’s necessary to attend to multiple clients trying to gain access to the same resource at the same time (aka concurrence issues).
Finally, there’s another big "NO" that MyISAM hides under the hood. Effectively, the mechanism doesn’t offer support for handling native foreign keys. Of course, I’m not saying that you won’t be able to create two or more tables and relate them manually at will through a few foreign keys. What I actually mean is that you can’t specify foreign keys in the corresponding table definitions and specify the constraints that they will behave in response to common cascade operations, such as updates and deletions.
In summary: MyISAM will perform satisfactorily as long as you’re database requirements remain basic and limited to running traditional, non-transactional CRUD operations. If you need to take a step further into the realm of transactions (which goes hand in hand with the trends of modern application development), it should be discarded in favor of a more robust implementation, such as InnoDB.
Added to MySQL in 2005, InnoDB (whose manufacturer is the Finnish company Innobase Oy [http://www.innodb.com]) is a remarkable improvement of its predecessor MyISAM, which not only saves records following the same order of their primary keys (something that sometimes avoids an additional reordering process), but it also offers solid support for performing transactional CRUD operations (the details on how InnoDB achieves this are out of the scope of this article, but you can take a deeper look at them here [http://dev.mysql.com/doc/refman/5.0/en/innodb-storage-engine.html]).
While the support for transactions is hard to beat when it comes to selecting the storage engine that fits your needs most efficiently, InndoDB provides a few other robust features that turn it into an even more appealing contender.
If you’re wondering what these extra features are, here’s a quick summary of them: first off, the engine implements a row-level blocking mechanism (unlike its older brother MyISAM, which blocks the entire table being accessed), meaning that the server will be able to perform concurrent operations in the same table, as long as they don’t target the same row. This is a really clever approach, which really shines on its own, especially if you have a lot of clients accessing the same target table.
Last but not least, InnoDB will let you easily specify (in the same table definition) which column(s) will be native foreign keys, as well as the behavior that they will have when updating and deleting rows in the parent table(s) (in other words, the foreign keys constraints mentioned in the preceding section).
Don’t worry – I’m not planning to cover how to define foreign keys constraints when using InnoDB; however, if you’re interested in learning more on this topic, just check out the tutorial that I wrote here (http://www.devshed.com/c/a/PHP/Updating-and-Deleting-Records-in-MySQL-Tables-with-Foreign-Key-Constraints/).
It goes without saying that MyISAM and InnoDB implement a host of additional low-level features, which are way to extensive to dicuss here. Nevertheless, if you only need a quick guide that will show you the most relevant facilities that each storage engine brings to the table, hopefully this tutorial will assist you in the analysis process.
Over the course of this article, I made a brief summary of the most relevant features offered by the popular MyISAM and InnoDB MySQL storage engines. As you saw through this quick review, the clear winner here is undoubtedly the latter, not only for being much newer than its competitor, but due to the fact that it provides a bunch of facilities that are hard to ignore when it comes to building database applications, including the support for transactions (a key one, in my humble opinion) and the implementation of a solid row-level blocking mechanism.
Naturally, if your database requirements are rather basic and you don’t need to use transactions, MyISAM is still a good option worth noting that will yield pretty satisfactory results as well. Regardless of this, InnoDB is rapidly becoming the choice of many users, because of the aforementioned benefits, which as I said before, are definitively along the lines of modern application development.
See you in the next MySQL tutorial!