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.

Example

We have a table employees with 5 data rows:

emp_no birth_date first_name last_name gender hire_date email
10001 1953-09-02 Georgi Facello M 1986-06-26  
10002 1964-06-02 Bezalel Simmel F 1985-11-21  
10003 1959-12-03 Parto Bamford M 1986-08-28  
10004 1954-05-01 Chirstian Koblick M 1986-12-01  
10005 1955-01-21 Kyoichi Maliniak M 1989-09-12  

Then we have 3 UPDATE queries:

UPDATE `employees`.`employees` SET `email` = '[email protected]' WHERE `emp_no` = '10001';
UPDATE `employees`.`employees` SET `email` = '[email protected]' WHERE `emp_no` = '10003';
UPDATE `employees`.`employees` SET `email` = '[email protected]' WHERE `emp_no` = '10004';

And suppose we need them to execute altogether in a batch.

MyISAM will process it like this:

  1. Initiate the 1st query and lock the entire table
  2. Finish the 1st query, unlock the table
  3. Initiate the 2nd query and lock the entire table
  4. Finish the 2nd query, unlock the table
  5. Initiate the 3rd query and lock the entire table
  6. 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:

  1. 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.
  2. Finish each query and unlock each row.

There are some other minor differences between MyISAM and InnoDB:

Feature MyISAM InnoDB
Full Text Indexes yes Since 5.6.4
Transactions no Yes
ACID property no yes
Foreign keys constraints no Yes
Faster count yes Since 5.7
Storage Limits 256TB 64TB
Design Simple Complex

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.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download TablePlus for iOS

TablePlus in Dark mode