MySQL Health Check: How To Upgrade From MyISAM To InnoDB For WordPress (Part 2 of 2)

Welcome back to our two-part MySQL Health Check series! In this part, we cover how to upgrade your WordPress website’s database from MyISAM To InnoDB using a variety of options.

There are quite a few different ways that you can update all of your database tables to InnoDB. We’ll be covering some of the easiest and most common methods below.

Although it’s very unlikely you will experience any problems when performing the following, please ensure that you take a backup prior to making any changes.

Using phpMyAdmin

1. Open up phpMyAdmin and select your database on the left.
2. Click the ‘Query’ tab at the top.
3. Copy and paste the following SQL query into the box, and then click ‘ Submit query’ on the bottom right. Remember to replace database-name with your actual database name.

SELECT CONCAT(‘ALTER TABLE ‘,TABLE_NAME,’ ENGINE=InnoDB;’) 

FROM INFORMATION_SCHEMA.TABLES

WHERE ENGINE=’MyISAM’

AND table_schema = ‘database-name‘;

1. This will provide you with a list of all of your tables to update to InnoDB. Select the ‘Show all’ checkbox, or update the number of rows to 500 to view all.
2. Click the small ‘+ Options’ above the list, and enable ‘Full texts’.
3. Scroll down the list, highlight all the rows, and copy.
4. Scroll back to the top of the page, and click the ‘SQL’ tab.
5. Paste in your list that you copied previously, and select ‘Go’.

You’re now done! You should receive a message that everything has been completed successfully. To confirm, click on your database on the left and view the ‘Type’ column for each table. They should all be showing as InnoDB.

If you would prefer to do each table one-by-one, you can also do this within phpMyAdmin. Simply click on the table that you would like to update, click on the ‘Operations’ tab, and you will then be able to change to InnoDB from the Storage Engine dropdown.

Using WP-CLI

WP-CLI is by far one of the most useful tools that a WordPress developer can have in their toolkit. It can do almost anything within WordPress, and running database queries to update your storage engine is no exception! 

If you’re not yet familiar with WP-CLI, you can take a look at our Getting started with WP-CLI guide to get up and running.

1. First, run a quick check to determine what tables of yours are MyISAM by running the following command:

wp db query “SHOW TABLE STATUS WHERE Engine = ‘MyISAM'”

That will give you a nice table output of all of your MyISAM tables (if any).

1. The following command will find all MyISAM tables, and then run an SQL query to update the storage engine on each to InnoDB:

for TABLE in $(wp db query “SHOW TABLE STATUS WHERE Engine = ‘MyISAM'” –allow-root –silent –skip-column-names | awk ‘{print $1}’) ; do wp db query “ALTER TABLE $TABLE ENGINE=InnoDB” ; echo “Successfully converted $TABLE to InnoDB” ; done

You’ll see scrolling output as each table is successfully updated to InnoDB.

1. Once the above command has run, you can then run a final check to confirm that all tables are now using InnoDB and not MyISAM

wp db query “SHOW TABLE STATUS WHERE Engine = ‘MyISAM'”

You should not see any output. To search for InnoDB tables, simply replace “MyISAM” with “InnoDB”.

wp db query “SHOW TABLE STATUS WHERE Engine = ‘InnoDB'”

Using A Plugin

One of the easiest ways to update your storage engine is using a plugin, especially if you don’t want to delve into phpMyAdmin or WP-CLI. 

Along with its amazing caching ability, image optimisation, and CDN options, the popular caching plugin LiteSpeed Cache offers a feature that will convert specific database tables over to InnoDB in a single click. 

To view and convert your MyISAM tables:

1. Simply install the plugin and then navigate to LiteSpeed Cache > Database, then scroll down the page until you see “Database Table Engine Converter”
2. Click “Convert to InnoDB”, as shown in the below image.

Unfortunately, the plugin does not yet offer a way to convert all of your tables in bulk. However you’re able to open each link in a new tab to speed up the conversion process. If you would prefer to do this in bulk, you’ll need to use either the phpMyAdmin or WP-CLI method.

We hope that this post has helped you to discover some of the differences between the two most common storage engine types, and why InnoDB is probably a worthwhile upgrade for your site!

As always, if you have any questions about this post or our shared hosting, VPS, reseller or dedicated server plans, simply call us on 1300 MY HOST (694 678) during business hours, or submit a ticket through our Support Portal and one of the crew will be in touch!