Convert WordPress MySQL Database Tables from MyISAM to InnoDB for Better Scale

Scaling WordPress websites to stupidly big levels is something that makes us very, very happy.

Over the past 10 years, I’ve overseen some fairly significant web applications, powered by WordPress, which pushed the envelope in terms of what the CMS can do.

One instance saw us taking 5,000+ XML (NewsML + other types) files pushed to us from financial PR companies. Big ones. Every day. We spun this digital straw into WordPress posts, 24 hours a day, like if Rumplestiltskin had been a web developer. These posts were pushed into Google Finance, Yahoo Finance, and etc, and therefore we had major numbers of database writes and major numbers of database reads to contend with.

We’ve also built automatically loading e-commerce websites, running WordPress + WooCommerce, driven by daily data dumps in CSV and in XML formats, with product numbers into the xxx,000 range, when attributes and product variations are taken into account – which they surely must be!

So, in the course of our peregrinations around the big WordPress world, we’ve learned a thing or two along the way. Some of the more advanced clustering, load balancing and generally cloudy lessons, we’ll save for our internal power nerd sessions and conferences, but there are lessons which those running and looking to tune up more standard WordPress installations can learn.

And this is how we feed what we know back into the Open Source community and help those at the other end of the learning curve sink their teeth into becoming WP tuners!

Today, I’m going to describe how to take a janky old WordPress website, and wring some performance out of its MySQL database, by changing the storage engine from MyISAM to InnoDB.

This is not going to be a discussion over which is better. I’m just going to cut through all that and say InnoDB delivers significant performance gains at scale. Usually. And end that.

But you’re here, right, so you knew that already. You just want to know how to do that. Read on!

How to Convert WP MySQL Tables Storage Engine from MyISAM to InnoDB

Note, for the purposes of this tutorial, and often in the wild, we’re going table by table, in order to make sure our operation completes successfully. There is a scripty way to do this, but that’s not a great way to learn, and, on massive tables (which is when this becomes important) its nice to do it one by one, otherwise, you have to build error logging and other gubbins in there.

Back up before.

Back up before.

Remember to back up your tables and database as often as is practicable, including and especially before doing something like this, which might corrupt your database, borking everything in a table, and generally breaking stuff.

Step 1 – Find out what version of MySQL you have

This is important. If you are running MySQL version 5.5 or greater, then you won’t have to worry at all about step two, which is concerned with Full Text Indexes.

Before MySQL 5.5, InnoDB did not support full text indexes, so any you have on your tables won’t work if you convert them. This doesn’t mean don’t convert, but it does mean, first drop those full text indexes.

Luckily, we’re here to help you find those full text indexes!

Step 2 – Find & Drop Full Text Indexes

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

SHOW INDEX FROM wp_posts;

This will show you the indexes. As long as none of them are in full text, you are good to convert this table.

If there is a full text index, then drop it by issuing the following command:

DROP INDEX index_name ON wp_posts

Insert the name of your full text index, and drop it. Then you will be good to convert this table.

Step 3 – Convert the Table’s Storage Engine

ALTER TABLE wp_posts ENGINE=InnoDB;

This will then complete, and you’ll have successfully completed the alteration for this table. Now, to do the other old tables too!

Rinse & Repeat

Repeat this as often as you find MyISAM tables. I tend to start with the biggest ones, and work down, but that’s not really necessary. Its just that often bigger tables are more of a treat to fix when something goes wrong, so I take them first.

Once you’ve got them all, you will be able to tune your My.cnf file and get this setup for InnoDB tables, in order to get things blazing.

Having tables in both InnoDB and MyISAM, which often happens with WP sites which have been around a while, means you have to support both in your my.cnf files.

In my opinion, it is optimal, and scales better, to have all your tables in one storage engine, and for that storage engine to be InnoDB. Then get as much power to your database server(s) and ensure this is configured correctly for your queries.

Simple(!).

5 thoughts on “Convert WordPress MySQL Database Tables from MyISAM to InnoDB for Better Scale”

  1. I manage a WordPress for a small city’s news blog that has been getting popular and suffering from “table level locks” constantly. After optimizing in so many different ways it was still an issue. Finally I came across this post and switched the DB tables from MyISAM to InnoDB. CPU load dropped from 100% CPU constantly to 10-0%. Amazing difference. Thanks!

    Reply
    • Hi Rando, that’s great to hear! Yes, depending on your environment and site, this step can have a huge beneficial impact, and only needs doing once, to be forever faster.

      Reply
  2. How does a full text index appear when you type “SHOW INDEX FROM wp_posts;” ?

    I guess we’re looking for a particular value “FULLTEXT” in the column, ‘Index_type’?

    So, you could say

    ‘show index from wp_posts where index_type = ‘FULLTEXT’;’
    to show only the indexes you need to work on.

    Reply

Leave a comment

No links of any kind allowed in comments - if you add any links, your comment will automatically be deleted.