WordPress Database Optimisation

This WordPress database optimisation technique is useful in general, but especially so if your website runs the popular WooCommerce shopping cart plugin. In just 2 steps, your pages will load at least 2 seconds faster, but often a lot more than that.

One of my clients owns a site that sells dresses and despite being hosted on a business-grade server and using various caching and minification techniques, the product pages we often slow to load. Often, the WordPress back-end (admin area) was also slow. Since page loading speed is a big factor in conversion rate optimisation, as well as search engine optimisation, this was important to sort out.

To find out what was going on, I first installed the Query Monitor plugin, which shows various issues in the PHP proccessing of the page, including slow queries. On every page load, it was showing one particular query as taking at least 2 seconds, often longer. That query was run by a core function to load all the options with “autoload = ‘yes’”.

Web discussions showed that other people were plagued by this, but the WordPress core team was opposed to addressing it with an index, or in any other specific way. I had to do it myself.

Before you read on, please note it is crucial to have a backup of your database handy, just in case.

Step 1: From table locking MyISAM to row locking InnoDB

The table wp_options used the MyISAM storage engine, which uses table-level locking. Since WooCommerce uses this table for caching sessions, and the table contained more thn 600,000 rows, there were frequent clashes between the insertion and updating of records and scanning the entire table for rows with “autoload = ‘yes’”.

So I changed the storage engine from MyISAM to InnoDB. Here’s how:

1. Log into phpMyAdmin
2. Select the database
3. Select the table
4. Switch to the Operations tab
5. Change the Storage Engine to “InnoDB”
6. Click “Go”

Step 2: Autoload index

To speed things up even further, I wanted MySQL to process only 682 rows (those with “autoload = ‘yes’”), instead of 600,000. This is precisely what an index does. Here’s how:

1. Switch to the Structure tab
2. On the 4th line for the “autoload” field, click “Index”

That same query now takes 0.01 seconds on every page and there are no more interlocked pages. Talk about WordPress database optimisation!

Final thoughts

I’ve seen recommendations to change storage engines to InnoDB on wp_posts and even all the  tables. This is probably good for sites where multiple people update and view posts concurrently. I applied this change on a large blog and it seems to run faster now.

Due to the level of locking, switching to InnoDB should produce a noticeable benefit on the WordPress back-end (admin pages), where adding and changing posts and settings no longer requires full table locking, so basically, it’s a good idea.

Here’s how (advanced, pleases be careful):

1. Replace “database_name” in the following query and run it in phpMyAdmin

SET @DATABASE_NAME = 'database_name'; SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME  AND `ENGINE` = 'MyISAM'  AND `TABLE_TYPE` = 'BASE TABLE';

2. Copy the resulting SQL statements (you may have to change the display options to show the full statements) and run them

Enjoy,
Gal