MyISAM vs InnoDB
MyISAM and InnoDB are the two most popular storage engines used in MySQL. We can’t really compare them but we can see the difference and explain why MyISAM is better for some situations, while InnoDB will be more preferred in some other cases.
The biggest difference between MyISAM and InnoDB is that InnoDB implements row-level locking while MyISAM uses table-level locking.
So, what is locking?
Locking is a database integrity practice implemented in MySQL to ensure the accuracy of the data being processed. In a simple term, when there’s a running query that modifies or deletes data from the data table, MySQL blocks access to data from any other queries until the query finishes. This way, data integrity is always protected.
MyISAM and InnoDB have two different locking methods.
In MyISAM, the locking method is called table-locking. It means when one or more data cell is modified or deleted, MySQL locks the entire table until it finishes. So any attempt to access this table data during this time will be blocked.
InnoDB implements the row-level locking which locks only the records being modified or deleted, while the other records are set free. So even when this query is not finished yet, user still can access other data records using different queries.
We have a table employees with 5 data rows:
Then we have 3 UPDATE queries:
And suppose we need them to execute altogether in a batch.
MyISAM will process it like this:
- Initiate the 1st query and lock the entire table
- Finish the 1st query, unlock the table
- Initiate the 2nd query and lock the entire table
- Finish the 2nd query, unlock the table
- Initiate the 3rd query and lock the entire table
- Finish the 3rd query, unlock the table.
Since three queries process three different data rows, InnoDB will initiate all queries at the same time while locking only the data rows that tied to each query:
- Initiate the 1st query and lock the 1st row, and initiate the 2nd query and lock the 3rd row, and initiate the 3rd query and lock the 4th row, altogether.
- Finish each query and unlock each row.
There are some other minor differences between MyISAM and InnoDB:
|Full Text Indexes||yes||Since 5.6.4|
|Foreign keys constraints||no||Yes|
|Faster count||yes||Since 5.7|
When to use MyISAM?
With the table-locking technique, MyISAM will be the best option when it comes to having a lot of read operations with frequent select queries.
When to use InnoDB?
InnoDB will outperform MyISAM in a situation where you need to insert, edit, update data frequently because the row-level locking technique allows multiple edit queries to run concurrently.
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download TablePlus for iOS