Differences Between InnoDB and MyISAM
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:
Strict Data Integrity
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.
Row Locking
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.
Allows Transactions
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.
Faster Indexing
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.
Native Recovery
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.