The difference between MyISAM and InnoDB

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
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:

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:

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.