MySQL Storage Engines: Why You Should Convert from MyISAM to InnoDB

Benefits of InnoDB over MyISAM

Row-Level Locking

Locking is a mechanism in MySQL that prevents multiple users editing the same data at the same time. This prevents one user overwriting another user’s changes. MyISAM uses the default table-level locking which would lock the entire table when one user is editing it – InnoDB uses a much more efficient row-level locking which means multiple users can edit the same table at the same time (just not the same row).

Foreign Keys

InnoDB supports foreign keys which are an attribute linking one table to a primary key of another table. This has both performance and troubleshooting advantages – MyISAM doesn’t offer foreign keys.

Transactions, Transactional History and Recovery

InnoDB supports transaction properties such as rollbacks and commits, along with offering a transactional log which can be used to recover data in case of failure. MyISAM doesn’t offer any data integrity which could lead to data corruption.

Check for MyISAM Tables

After logging into phpMyAdmin and clicking on the database, you can generally see which storage engine you’re using in the"Type" column, but if you have a large number of tables and want to check for any which are still MyISAM, you can run the following query:

DISCLAIMER : This can only be achieved on databases running MySQL version 5.6.4 or higher (most databases will be).

SELECT TABLE_NAME,
 ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database' and ENGINE = 'MyISAM'

If you have identified a MyISAM table and want to convert it, you can run this query:

ALTER TABLE wp_comments ENGINE=InnoDB;

There are also a number of WordPress plugins which will do this for you. I’d recommend Servebolt Optimizer. This is the sort of thing my care plans handle each month.