We often see that are questions regarding the use of the InnoDB storage engine as compared to the MyISAM storage engine. We have constructed a table to illustrate common differences between them.

Feature                             InnoDB             MyISAM
Strict Data Integrity          Yes                    No
Row Locking                     Yes                    No
Allows Transactions         Yes                    No
Faster Indexing                 No                    Yes
Native Recovery               Yes                    No

Now, to break that down a bit further and explain why these features are important to note:

This refers the dependencies of the MySQL directory itself with regard to the files and how they relate to the database as a whole. InnoDB requires that tablespace values of .ibd files correlate to the ibdata file. Where MyISAM does not. MyISAM doesn’t have a dependency in this regard which makes replacing the database files themselves rather easy, but at the cost of the affected table being somewhat disassociated from the whole. While this is not that big of a deal with MyISAM since InnoDB utilizes transactions it is very important that the tablespaces match.

A point of note for the InnoDB engine compared to the MyISAM engine, InnoDB can lock a single row for interaction, where MyISAM will lock an entire table while it is interacting with a row to retrieve data. This correlates directly with InnoDB’s utilization of transactions. Transactions could be rather cumbersome if you were attempting to interact with separate row data in the same table if the InnoDB engine locked the entire table.

The key feature of the InnoDB engine it that it allows the use of transactions. A transaction is a method of running multiple interacting and somewhat dependent queries in a single action. This is not available for MyISAM tables.

The primary benefit of using MyISAM tables is that they can be more efficiently indexed, where InnoDB is somewhat trapped by the use of the gen_clust_index, which is essentially a centralize index that will correlate to indexes whether unique or not. Meaning indexing in InnoDB is essentially double indexing (double-checking). While indexing is always recommended for heavy-read tables, it is technically slower in an InnoDB environment when compared to the MyISAM engine, especially the larger the database gets.

In MyISAM, when a command is run it is all over. That command is final and there is nothing that you can do about it besides overwriting the file with a previous instance. In an InnoDB environment, you can utilize the ROLLBACK statement, which, in a transaction, allows for failed commits to be reverted sparing your database from empty or incorrect data.