How to convert WordPress database table from MyISAM to InnoDB?

I use WP-Optimize plugin on one of my WordPress site run database optimization actions. Besides that, WP-Optimize can also cache your site, cleans your database, compresses your images. It also helps me to remove zombie table left behind by uninstalled plugins. Now, while exploring database tables today using WP-Optimize, I found that my wp_posts table was MyISAM and not InnoDB like the rest of the tables. In this post, we shall discuss how to convert WordPress database table from MyISAM to InnoDB.

This post is not about MyISAM vs InnoDB or which one is better. Anyways, MyISAM was a default storage engine until Dec 2009; later, InnoDB replaced the default storage engine in MySQL. 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. There are various articles on this topic online. So you can check out if you are interested further. To cut short, InnoDB delivers significant performance gains than MyISAM engine.

So, if you are here with a similar issue How to convert WordPress database table from MyISAM to InnoDB, then read on.

1) Find out if your WordPress database tables are using InnoDB or MyISAM

You can find out if your WordPress database tables are using InnoDB or MyISAM using WP-Optimize plugin if you are using one. Otherwise, you can also find out using phpMyAdmin. After getting into phpMyAdmin, just look at the type column corresponding the tables, OR you can also find by running the query:

If you are using WP-Optimize plugin, while you are logged in as the admin user in WordPress, do the following –

• Click on DATABASE under WP-Optimize > and then click on TABLES tab
• Scroll through the tables and look at the TYPE column – it will show the DB engine type. In my case, it is mostly InnoDB but one table in particular, ngk_posts has the MyISAM type.

• In the same page, adjacent to the table with MyISAM type, WP-Optimize plugin provides a button to convert to InnoDB. But that did not work as some error message is thrown.

2) Find out what version of MySQL you are using

This is important because if you are running MySQL version 5.5 or greater, then you don’t need to worry about the next step, which is concerned with Full Text Indexes. Before MySQL 5.5, InnoDB did not support full text indexes.

You can find the version of MySQL from WordPress > Tools > Site Health > Info > Click on the Database.

You should see something like this:

Server version

5.5.68-MariaDB

3) Before you proceed any further – BACKUP YOUR DATABASE!!

4) Find & Drop Full Text Indexes

Now that we know that which tables are using MyISAM from step 1,  follow the steps below.

Go into your MySQL, or run the following in PHPMyAdmin for each table which uses MyISAM. Here I’m using ngk_posts as an example:

SHOW INDEX FROM ngk_posts;

This will show you the indexes. What you should look for is the index_type column.  As long as none of them are in full text, you are good to convert this table.

As you can see in the screenshot below, I got some FULL TEXT indexes and that is from ‘Contextual Related Posts’ plugin I am using which I can identify from the prefix ‘crp_’.

If there is a full text index, then you need to drop it before converting to InnoDB by using the following command:

DROP INDEX index_name ON ngk_posts;

If you succeed, then the next step is to convert the engine from MyISAM to InnoDB by executing the command on the table –

ALTER TABLE ngk_posts ENGINE=InnoDB;

If you are not comfortable running queries, click on OPERATIONS tab in phpMyAadmin and change the storage enginer type from MyISAM to InnoDB and then click GO.

With that, you have successfully completed the alteration for this table from MyISAM to InnoDB. You need to repeat the same steps for other tables with MyISAM.

---

In my case, I executed the following commands –

• DROP INDEX crp_related ON ngk_posts;
• DROP INDEX crp_related_title ON ngk_posts;
• DROP INDEX crp_related_content ON ngk_posts;

but the result was ‘MYSQL returned an empty result set‘ – the query is getting executed by the FULL TEXT indexes are not getting removed. And on another attempt, I started to get some primary key exceptions.

So I decided to check the plugin which is responsible for this – ‘Contextual Related Posts‘ plugin.

• I click on PLUGINS > Looked for SETTINGS at ‘Contextual Related Posts‘ and evidently, the plugin is using FULL TEXT Indexes.
• Checked the following to delete Full Text Indices  on UNINSTALL / DEACTIVATE.
• Then deactivated and uninstalled the ‘Contextual Related Posts‘ plugin.

• Went back to phpMyAdmin and executed the querySHOW INDEX FROM ngk_posts; and the FULL TEXT indexes previous found are no more.
• Next executed the command ALTER TABLE ngk_posts ENGINE=InnoDB; I suppose the ‘Convert to InnoDB’ from WP-Optimize should also work now that there are no FULL TEXT indexes.
• With that, the table is converted to InnoDB from MyISAM.

WP-Optimize also confirms that the table is now InnoDB as shown in the screenshot below.

As discussed above, to convert WordPress database table from MyISAM to InnoDB, we need to find the tables which are MyISAM, then drop the indexes with full text indexes, then convert to InnoDB, which you can do using the phpMyAdmin GUI or using the command above.

However, if you face challenges in dropping full text indexes like the issue described above, then find  the cause, and then proceed. You may need to uninstall the plugin causing the problem like mentioned above.