Case study: optimizing a WordPress & WooCommerce site's database

In this article, I’m going to walk you through a case study of optimizing one of our clients’ WooCommerce site’s database for better performance. If this is a totally unfamiliar topic for you, you should check out a basic tutorial such as this one from WPMU Dev before reading further, as we’re going to skip the basics here.

For context, the site is a legacy build, 4-5 years old, currently running WooCommerce 3.5.7 with a bunch of various plugins, a custom-built theme and a few pages made with a page builder. The site is clearly set up by someone with very little experience on the subject, as no best practices have been followed and a lot of different low quality plugins have been used. Also note that at the time of writing, latest Woo is 3.8.1, which means that the site has not been updated for a while.

So it’s a typical WP site.

I’ve divided the case study into reasonably ordered steps, so you can follow them to optimize a database by yourself.

Let’s get started!

Step 1

Make a backup of the database!

Step 2: review tables

Fire up your favorite SQL software and take a look at the tables. I’m using PHPMyAdmin here, as the site is running on a shared host and this is the easiest way.

Order the tables by size and take note of the number of rows and the size of the tables.

The largest tables are

• wp_postmeta with 1.6M rows, weighing 300MB;
• wp_options with 250k rows, weighing 230MB;
• woocommerce_order_itemmeta with 1M rows, weighing 130MB.

Interpreting these results depends completely on the site. This site has 30k orders, 6.2k users, 2k comments, 1.5k products, 127 blog posts and 60 pages, so in this case, the large postmeta and ordermeta tables are not surprising.

Now, let’s go over the biggest tables and analyze their contents.

wp_postmeta

This table contains all the metadata of products and orders, so I doubt there’s much I can do to optimize it. Still, I took a look inside.

First, I wanted to check what are the most common meta keys. As a sidenote, I should mention that I suck at SQL, so I had to google almost every query described in this article. So even if you’re not super familiar with SQL, just assume that you can use it to get answers to almost any questions related to the composition of the tables – and then just google til you find a way. However, make sure you test unfamiliar queries in an environment that is not production.

Anyway, here is the query to count the numbers of meta keys:

SELECT COUNT(meta_key), meta_key FROM wp_postmeta GROUP BY meta_key ORDER BY COUNT(meta_key) DESC

Look for anything unusual in the results, such as leftovers from old plugins or duplicate metadata (i.e. if you have 30k orders, duplicate metadata means there would be a lot more than 30k of some order-related meta).

Looking at the top results, almost all of the data belongs to the 30k orders on the site. Everything below 14k until about 1.5k (not shown on the screenshot) were mostly product metadata and there was no point going below that. The only thing I could clean up here was wpml_language, as WPML is no longer used on the site – but considering the number of rows, this is a trivial optimization.

wp_options

This table is accessed multiple times on every page load because it stores all of the site’s settings. In addition to settings, it also stores transients (basically any kind of cached data). So pretty much every plugin ever installed will write its configuration settings into this table and most likely not clean it up after uninstalling. In most cases, this is not really an issue – an older WP site might have a few hundred extra rows in the table, but this will have negligible impact on performance. However, lazy or beginner plugin developers can still screw up this table and impact the site’s performance, so this is a table you should check every time your site has performance issues.

In this case, we have 250k rows that weigh a total of 230MB. Even though this is not a very small site, that is a bit too much and I have a gut feeling that something is spamming the table with useless data. So let’s check what’s causing this.

We’ll start from somewhere in the back. The first 1-2k rows are usually legitimate core & plugin settings and after that comes various other data and transients. I opened up a random page in PHPMyAdmin and saw this:

After checking a couple more random pages sufficiently far from each other and seeing the same thing, I assumed that most of the 250 000 rows consist of these specific transients you can see on the screenshot. To double-check, I ran the following query:

Indeed, for some reason there are 235 000 mostly empty transients in the database. I had no idea what exactly they were, so I googled the name and found out that they were WooCommerce’s “customer bought product” transients. The linked support thread describes a similar problem to mine. WooCommerce support is not helpful at all in that thread:

In your site Dashboard, please navigate to WooCommerce → Status → Tools tab. From there you should see the first two options are for clearing transients in your site. Please use these tools and test to see if the issue is resolved.

Yes, thank you, but how do I actually solve the root cause of the issue?

Anyway, since Google gave me only 4 results, it looks like there aren’t any more complaints about this specific issue. Therefore, it must have been an edge case bug which has probably already been fixed by now in a later release. I was going to update the site later anyway, so I simply cleaned up those specific transients.

DELETE FROM wp_options WHERE option_name LIKE "%_transient_wc_cbp_%"
DELETE FROM wp_options WHERE option_name LIKE "%_transient_timeout_wc_cbp_%"

Results:

Much better!

By the way, issues with flooding wp_options are common with WooCommerce (and in WP ecosystem in general). For example, in early 2019, under certain conditions WooCommerce would almost crash some sites by flooding the “cron” row in wp_options with as much data as the field could contain. So on most page loads, WP would try to read 5MB of data from a single table row and then write a bigger value into the same field, which would cause a database error and crash it. Again, this was not a very common issue – just a couple of results on Google – but I encountered it on two of our customers’ sites hosted at the same host, so I’m guessing it had something to do with the server environment.

Anyway, moving on.

wp_woocommerce_order_itemmeta

Here I ran the same query I ran previously for the postmeta table, looking for similar patterns: meta with a suspiciously high count or meta left behind by old plugins.

Everything looks good here.

wp_comments

A quick look into wp_comments confirmed that most of them are WooCommerce order notes – so it makes sense to have so many rows. I also checked for any spam comments and reviews, but there were none.

wp_usermeta

This is also a table that is accessed on every page load for logged in users, so it makes sense to briefly check it out. There is a good chance that some old plugins may have used this table for storing something.

Here we have WP core data, WooCommerce profile data and just a couple of fields from plugins. Nothing to do here.

Other tables

There’s not much point in digging into the rest of the database tables, as they’re small enough to not cause any problems.

Step 3: clean up wp_options further

As you might remember, the wp_options table is a special case – it’s used on each and every page load – and thus requires further attention.

The “autoload” column

The most important thing to understand about this table is that rows which have the “autoload” value marked as “yes” will be loaded on every page load ever.

The second most important thing to understand is that the default value of $autoload parameter in update_option() function is “yes” for new values (for existing values, the autoload param won’t be changed).

Yep. This means that if a plugin developer forgets to specifically mark $autoload as “no,” the option they created will be autoloaded.

So now you know that a very large number of plugins happily autoload all of their configuration data.

I’ve seen this backfire in a couple of ways. A few years ago, a themeforest plugin for sending abandoned cart emails stored each abandoned cart’s contents in wp_options table, with autoload set to yes. This meant that hundreds to thousands of rows were autoloaded on each request. In addition to slowing the site, this ended up constantly crashing the site’s Redis cache, as Redis also had to store an extra 2MB of data in memory for every page load. I’ve also seen plugins store hundreds of kilobytes of text or serialized arrays in wp_options (*cough* WPML). This has a similar impact.

How to fix overly eager autoloading

Run the following query (Thanks Peeter Marvet for an improvement here!):

SELECT *, LENGTH(option_value) FROM `wp_options` WHERE autoload = 'yes' order by LENGTH(option_value) DESC LIMIT 50

This will show you the top 50 rows with biggest option_value columns, ordered by their length.

Note that 1 character is usually 1 byte, although this is more with special chars and non-generic encodings. As a rule of thumb, the values here usually shouldn’t be over 10kb. However, sometimes it makes sense to store bigger values there. You’ll need to go through the results case by case and investigate what these options actually are for and then figure out what you can do about them.

As an example, on the site I was debugging, the first result was “wp_installer_settings” which was 130kb of encoded text (it’s not visible on the screenshot above because I deleted it shortly before realizing I should write an article about this topic) – here’s the contents:

I had no idea what this was, so I just googled it. Turns out it’s some sort of value used by WPML.

I didn’t want to waste any time figuring out what this thing is exactly, but I skimmed through the support thread. An excerpt:

You can also disable *wp_installer_settings* and *wpml_translation_services” for now we think those are not needed on each page.

“We think those are not needed.”

The site used to run WPML a long time ago but no longer does (remember what I said about plugins not removing the metadata after uninstall?). So in this case, I was able to simply nuke this row.

The next option you can see on the screenshot is “_s_category_pricing_rules.” A quick google search returned this github repo so it looks like this is a value required by WooCommerce Dynamic Pricing – which was an active plugin on the site.

The strange value here means that it’s serialized (i.e. it’s an array converted to text so that it can be stored in the database). You can copy-paste it into an online unserialize tool to get a better understanding of what’s inside. In this case:

Array
(
    [set_1454] => Array
        (
            [conditions_type] => all
            [conditions] => Array
                (
                    [0] => Array
                        (
                            [type] => apply_to
                            [args] => Array
                                (
                                    [applies_to] => everyone
                                )

                        )

                )

            [collector] => Array
                (
                    [type] => cats
                )

            [rules] => Array
                (
                    [0] => Array
                        (
                            [type] => 
                            [amount] => 
                        )

                )

        )

    [set_53] => Array
        (
            [conditions_type] => all
            [conditions] => Array
                (
                    [0] => Array

(and so on)

I’ve never worked with the plugin before, but looking into the admin page, the plugin settings page has pricing rules for each role as well as each category. There are lots of categories and roles on the site and most don’t have active pricing rules, so I’m assuming each of the “sets” is a role or category or something like that. It’s quite annoying that the developer stores empty values serialized in the admin interface, because there’s 26kb of data there, most of which is just empty array keys. However, product prices are required on most of the pages on an e-commerce site, so I’m just going to leave this untouched for now.

Next up: “rewrite_rules.” This one I’m familiar with – it stores all URL patterns your WP site should react to. Since the site has a lot of categories and tags and each one has to be specified in this value, the total size of that field is 25kb. However, this is WP core functionality, so I’ll leave it untouched.

Next, “wpurp_options.” For this one, I just googled the prefix “wpurp” which returned a bunch of links for WP Ultimate Recipes plugin.

Sidenote: at Codelight, we rarely use 3rd party plugins for simple functionality such as a custom resource post type, but as I mentioned before, this specific site was built by someone else.

The Ultimate Recipes plugin has decided that 13kb of data must be loaded and stored in memory on each page load. After unserializing it, I determined that it contains all of the plugin’s options:

Array
(
 [recipe_template_premium_not_installed] => 
 [recipe_template_open_template_editor_disabled] => <a href="#" class="button button-primary button-disabled">Open the Template Editor</a>
 [recipe_template_editor_recipe] => 
 [recipe_template_recipe_template] => 0
 [recipe_template_print_template] => 1
 [recipe_template_recipegrid_template] => 2
 [recipe_template_feed_template] => 99
 [recipe_adjustable_servings] => 1
 [recipe_adjustable_servings_fractions] => 
 [recipe_default_servings] => 4
 [recipe_linkback] => 
 [recipe_images_clickable] => 1
 [recipe_instruction_images_title] => attachment
 [recipe_ingredient_links_premium_not_installed] => 
 [recipe_ingredient_links] => archive_custom
 [recipe_ingredient_custom_links_target] => _blank
 [recipe_ingredient_custom_links_nofollow] => 
 [print_template_title_text] => [redacted]
 [recipe_rss_feed_display] => excerpt
 [recipe_rss_feed_shortcode] => 1

(and so on)

This is generally not a very good approach. If any page load requires just one or two options, keeping them in one array means that all of them have to be loaded. It’s not a big deal in most cases, though.

In our case, we definitely don’t need data for recipes loaded on every page, so what I did was change the autoload parameter to “no.” So even if this value is updated using update_option(), unless the developer has specified that this data must be autoloaded, the autoload value will stay on “no” in the future.

I addition to that, I changed the autoload values of all other options with wpurp_ prefix to “no.”

Next, we have some user role data (this is from WP core), then some bloat from WPML (everything starting with icl_ or wcml_) and then “otgs_active_components” which is just 7kb. In this case, below 10kb is no longer worth the time to optimize, so let’s move on.

The number of autoloaded rows

Let’s also check how many autoloaded rows there are.

SELECT * FROM `wp_options` WHERE autoload = 'yes';

Not great, not terrible. Half of these are probably from old plugins, but it’s just not worth cleaning up. Just in case, I quickly scrolled through all the 700 rows and looked for any repeating patterns (e.g. abandoned_cart_[IDENTIFIER]) to see if there’s anything creating unnecessary autoloaded rows there, but there was nothing like that.

At this point, any further debugging would not have a very good return on investment, so that’s where I stopped.

Step 4: The OCD cleanup

The last step is to get rid of everything that’s no longer used. This has almost no impact on performance at all, but cleaning up unneeded tables and rows reduces the amount of information the next developer (or you yourself) have to process in order to understand how the site is working. Still, don’t spend too much time here.

I took the following steps:

• Cleaned up all tables created by WPML (prefixed with icl_ – you’ll want to remember this one).
• Googled the prefixes of all other non-core tables (such as pmxi_), figured out which plugin they belong to (pmxi_ belongs to WP All Import, in case you’re wondering) and deleted them if they were no longer used.
• For the prefixes that didn’t turn up anything useful in Google, I searched through the code base of all plugins (you can use PHPStorm’s code base search or alternatively just grep).
• Deleted all items with the prefixes found in the last 2 steps from wp_options.
• Ran all the typical database optimization steps – you can just google them or refer to the WPMU guide again.

And that’s all!

Final thoughts

Optimizing WordPress databases manually like this is usually not very time consuming and can result in noticeable site speed improvements. In my case, the only serious improvement was removing the unnecessary transients in the database – everything else had an almost trivial impact. However, going through a database like this doesn’t take too much time if you’ve already done it a couple of times and are familiar with the default tables and keys used by WordPress and WooCommerce. For me, it would have taken just about half an hour if I had not written this post in parallel.

So go forth and optimize. Just don’t forget backups.