How MySQL InnoDB vs. MyISAM data engines compare
When MySQL 5.5 switched its underlying storage engine MyISAM to another, InnoDB, it drew questions and created some confusion about the foundation of this database.
Certainly, the choice of a storage engine to power a modern relational database has consequences for database write speed, read optimizations, scalability and transactional integrity. When organizations choose a traditional database, like MySQL, over a NoSQL store or a graph-based option, it's typically because there is a need to support relational database concepts, like foreign keys, inter-table relationships and transactional support.
These three concepts are built directly into the InnoDB database but aren't native to MyISAM. It simply makes more sense to have a storage engine that supports relational database concepts beneath a relational database system. Hence, the birth of the MySQL InnoDB vs. MyISAM debate.
Of course, MySQL users prior to the 5.5 release were probably perplexed by the assertion that the database didn't support foreign keys. MySQL has always supported foreign keys, inter-table relationships and SQL usage to query data in a relationally structured way. But it was always the MySQL shell that existed as a layer above the database engine that powered these functions, not the database engine itself.
The migration from MyISAM to InnoDB makes the logical layer above a symbiotic partner with the database layer below. It's a much more architecturally sound approach to building a relational database system.
MySQL InnoDB and transactions
Transactional support is a key difference between MySQL InnoDB vs. MyISAM. If a problem occurs when you write data to an InnoDB engine, you can roll back any changes to their prior state to ensure that all operations are performed in an atomic, all-or-nothing fashion. On the other hand, if you partially write data to a MyISAM database and a failure occurs mid-write, the system is left in an inconsistent state where some data persisted, and other parts did not. MyISAM can't support ACID-based transactions because it doesn't allow for atomic operations and rollbacks for failures. InnoDB can handle ACID-based transactions.
InnoDB can also keep data integrity in the system and reliable transactional commits with three additional features:
- A transaction log that keeps a history of changes;
- A checksum to monitor for data corruption; and
- An optional doublewrite buffer that can recover data lost in a subsystem crash.
MySQL InnoDB vs. MyISAM performance
You can explain most of the performance differences between MySQL InnoDB and MyISAM with a comparison of how the two engines access data. InnoDB has a coarse-grainedtable-based approach to data access and locks each table as it is queried. MyISAM takes a more granular approach and only locks individual rows, not the whole table.
For example, multiple clients could concurrently access various rows in a MyISAM table. This approach uses idle hardware resources to improve performance. Given the same scenario in InnoDB, it places each user in a queue based on the initial request and waits for the table lock to end.
MySQL InnoDB vs. MyISAM misconceptions
One fallacy of MySQL InnoDB vs. MyISAM comparisons is that InnoDB doesn't support full text searches while MyISAM does. This statement was true at one point but since the release of MySQL 5.6, InnoDB supports full text searching.
InnoDB is a more sensible choice for a relational-based system, but there are comparisons in which MyISAM does come out ahead. Because of the way MyISAM structures its data, certain queries -- such as joins, groupings and count-based clauses -- will perform faster. Furthermore, MyISAM is better suited to high-level data compression for read-only data, which can be beneficial if disk space is an issue.
There are indeed specialized uses in which the MySQL InnoDB vs. MyISAM comparison isn't completely cut and dry. As always, the key is to know your data, know how it's accessed and choose a database architecture that best suits your needs.