InnoDB vs MyISAM: Which Is Better?

January 30, 2020

The two most used storage engines in MySQL database management system are InnoDB and MyISAM. These engines are quite different though, and both have advantages and disadvantages. If you’ve been wondering about InnoDB vs MyISAM, which one of them you should choose, this post explaining what their main differences are should help you.

 

Locking

The main difference here is that MyISAM uses table-locking while InnoDB uses row-locking. This means that when a query is running through any of the rows on a MyISAM table, the whole table is locked. InnoDB is different, as it uses row-locking. So, when a query is running through one of the rows, only that one row is locked, other ones remain open.

 

Speed

In general, the performance speed of a MyISAM table is much higher when compared with tables in InnoDB. Speed depends on the locking issues that were shortly discussed in the previous paragraph. InnoDB vs MyISAM readability speed is much lower mostly because figuring out which rows are locked in a table takes more time than just locking the whole table.

 

Transactions

MyISAM does not support transactions, while InnoDB does. This means that when an operation is interrupted on MyISAM, it is aborted, and the data that was already affected remains affected. With InnoDB, when a running operation is interrupted, any transaction which did not go to completion will not take effect.

 

Data Integrity

Various hardware failures, cancelled operations and unclean shutdowns can corrupt data in MyISAM, which means that in such event a full repair or a rebuild of indexes and tables would be needed. InnoDB, though, solves this problem beforehand. Before any changes are made in InnoDB, it records the data into a system tablespace file which is called ibdata1. If, unfortunately, a crash happens, InnoDB can automatically recover by replaying the transaction logs.

InnoDB vs MyISAM choice really depends on your personal preference and needs, as both of these storage engines have some advantages. MyISAM is the default engine for MySQL, but InnoDB has its own advantages too, so, consider your specific needs and then make a choice best suited for your situation.