MySQL Storage Engine – How to Convert MyISAM to InnoDB

One of the biggest reasons that large-scale WordPress sites slow down is because their database hasn’t been optimized. A lot of older sites are still using the MyISAM storage engine in their database. Over recent years, InnoDB has shown to perform better and be more reliable.  A big reason to use InnoDB over MyISAM, is the lack of full table-level locking. This allows your queries to process faster.

InnoDB vs MyISAM

Here are a few of the major differences between InnoDB and MyISAM:

• InnoDB has row-level locking. MyISAM only has full table-level locking.
• InnoDB has what is called referential integrity which involves supporting foreign keys (RDBMS) and relationship constraints, MyISAM does not (DMBS).
• InnoDB supports transactions, which means you can commit and roll back. MyISAM does not.
• InnoDB is more reliable as it uses transactional logs for auto recovery. MyISAM does not.

Are You Using MyISAM or InnoDB?

If you are running on a fairly new WordPress site chances are you are already using the InnoDB MySQL storage engine. But with older WordPress sites you might want to do a quick check. Some sites might even have mixed and matched MyISAM and InnoDB tables, in which you could see improvements by converting them all over.  Follow these simple steps below to check.

Step 1

Login to phpMyAdmin and click into your mySQL database.

Step 2

Do a quick scan or sort of the “Type” column and you can see which Storage Engine types your tables are using. In this example below, you can see that two of the tables are still using MyISAM.

Alternatively, you could run a query to see if any myISAM tables exist. Replace ‘database’ with your database name.

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

Convert MyISAM to InnoDB with phpMyAdmin

You can convert MyISAM to InnoDB fairly easily. This example is below is using the wp_comments table. Simply run the ALTER command to convert it to InnoDB storage engine. Note: We always recommend backing up your MySQL database before running any operations on it.

ALTER TABLE wp_comments ENGINE=InnoDB;

Ensure you are running MySQL 5.6.4 or higher otherwise, you might run into issues where full-text indexing is not supported yet by InnoDB. If you are a Kinsta client you don’t need to worry about this.

Alternatively, you can also convert them manually with phpMyAdmin. Simply click on the myISAM table, click into the “Operations” tab, and change the storage engine.

Convert MyISAM to InnoDB with WP-CLI

WP-CLI is a command line tool for developers to manage common tasks (and not so common) of a WordPress installation. Check out this article on how to convert MyISAM to InnoDB with WP-CLI.