There are two main components to a WordPress Site. The webserver files and the database files. The webserver files contain things like the images, the plugins and theme files, and the WordPress core files. The database contains things like the core, plugin and theme options, the post content, user content and meta tables. The two communicate with each other constantly and yet when optimising, the database is often overlooked which is sort of like upgrading your peripherals to an ultrawide curved monitor with 7.1 surround sound and an ergonomic mechanical keyboard along with the latest optical mouse but not updating your actual machine in 15 years. Everything looks and feels nice but something is a little off and smells weird.

Since a site’s database needs to be password protected (it houses all the user info), no third party scanning tools can can access it. This means that staple “go-to’s” like GTMetrix, WEBPAGETEST, Pingdom Tools or PageSpeed Insights could give you a high score across the board while your database remains in a very poor state. You can use a plugin like Query Monitor if you’d like to get a closer look at your queries but it won’t be required for the purposes of this optimisation. For this one, we’re going to be using the WP-CLI pretty extensively so make sure you’re connected to your server via SSH. Your hosting provider should be able to point you in the right direction if you’re unsure on how to connect and I appreciate that the command-line interface can be a little intimidating for some users so almost every command referenced can be copied and pasted as-is and executed from your domain directory using the WP-CLI alone. If you haven’t done so already, I’d also recommend checking out my article on how to optimise your WordPress installation which covers the webserver side of things and can be found here.

Database Structure

When we talk about database optimisation, it’s important to understand what your database actually is. In effect, it’s just a block of information sorted by rows and columns. When your site needs a specific piece of information, it looks for the assigned row and pulls what it needs from the columns. The user tables are a great way to illustrate this as a concept and for the sake of clarity, I’ve created a simplified example of one below;

In the above example, let’s say John wants to login. He enters his user_login and password. WordPress searches this table for the user_login entered and once found, queries column 3 of the appropriate row to make sure the credentials are correct. If they are, WordPress then queries column 2 for the display name which it wants to print in the top right of Johns screen and column 4 to know which parts of the wp-admin page John should have access too.

In this example, there are only two users so the queries won’t take any time at all, but imagine if there were 40,000 users when that initial search happened. In that case, there would be a notable performance difference. WordPress stores all of it’s data in the above format and like any database, it can become bloated over time. When we optimise a database, what we’re actually doing is removing unnecessary rows and introducing order to chaos to reduce the time each database query needs to operate.

Before Starting

MAKE A BACK UP OF YOUR DATABASE.

Before performing any destructive action on your database make sure you have a backup. Many premium hosting packages include on-demand backups and there are many options when it comes to backup plugins so I won’t list them all but personally, I like to create mine straight from the WP-CLI using the following command:

wp db export ~/bak_`date +"%m-%d-%Y-%T"`.sql

This creates a .sql file in my user directory that always begins with “bak_” and ends with a date and timestamp of when I executed the command. If you ever need to restore from that backup you can execute:

wp db import ~/bak_$date.sql

Just be sure to replace “bak_$date.sql” with the correct filename!

After that I have a quick peek at the overall size of tables so I can formulate a plan on where to start with the following command;

wp db size --size_format=mb --all-tables

The options Table

If you’ve never worked hands on with a WordPress database before, you may be unsure of what you’re dealing with so let’s start there. The options table arguably contains the most important data for your site. Things like the site’s URL in a browser, the path to where the files are stored, all of your saved settings… you get the idea! On top of this it also commonly contains the settings of your plugins and theme and transient data which is intended to be used as temporary data with an expiration time. Unfortunately however, the double-edged sword of opensource software is that not all the developers are on the same page. This means that some plugin devs don’t remove their options data when the plugin is uninstalled and some don’t set realistic expiration timers on their transients which can lead to significant accumulation over time.

Deleting Transients

Just to be absolutely clear, transients are actually a very good thing. They allow developers to effectively cache data in your database that would otherwise take a while to generate. A common use case for this is API calls where the response from the remote server is stored as a transient so if your WordPress install needs it again, it doesn’t have to make another request of that remote server. The problem, unfortunately, lies with some developers implementations of transients. Instead of approaching it from the perspective of “My plugin might need this again so I’ll make a transient for 24 hours”, they are in the camp of “Hey, let’s just make the transient last as long as possible so our plugin has smooth sailing, what’s 100 more transients, they’re only a few KB in size!”. If everyone does the latter, those few KB can really pile up.

The handy part about transients though, is that if a specific function looks for one and it’s not there, it will just generate the info it needs into a new transient and go from there. This makes deleting transients worry free and easily manageable. To delete all your transients from the command line just enter wp transient delete --all to actually remove them and then wp db optimize to remove to effectively defragment your database. and you’re all set! If you find that your transients build up faster than you’re testing your site’s performance then it would probably be pertinent to create a scheduled event to clear them out daily.

Execute the crontab -e command to edit your crontab and add the following snippet:

00 02 * * * wp transient delete --all --path=/path/to/domain/directory/ ; wp db optimize --path=/path/to/domain/directory/

I won’t go into too much details about exactly how the crontab works in this article but in plan language all the above snippet actually does is:

On minute 00 of hour 02 of any day of any month of any year, run the wp transient delete --all command in my domain directory and when that’s finished, defrag it for me. Just be sure to update /path/to/domain/directory/ and you shouldn’t have any problems.

NOTE: Some hosts have integrated controls for user crontabs so if you have any trouble with this optional step, be sure to check their knowledge base to confirm!

After you’ve removed the transients and ran the “optimize” command, check the size of your tables again and take specific note of the difference in the options table size. When I’m speaking to clients about their database a lot of them will ask “What size should it be?” but it’s important to understand, that there is no hard and fast limit on databases. Instead you should think of them in ranges similar to the below (each of these entries assumes the database has already been optimised):

If your options table is in either of the last two categories, I’d suggest working with a professional WordPress developer to get things back on the right path.

Autoload

Now that you’ve reduced the overall size of your options table, it’s time to look at what’s left. The options table is unique in that it contains a set of data which is loaded with every request called autoload. If a site has a particularly large amount of autoload data, most users experience very poor /wp-admin performance which can affect the public facing site as well if it’s not cached. To find out how much autoload data your site has, execute the following command:

wp db query "SELECT SUM(LENGTH(option_value)/1048576) as 'Autoload(MB)' FROM $(wp config get table_prefix)options WHERE autoload='yes' ;"

If the data amounts to anything below about 600KB to 900KB you should be in a good spot. For site’s on extremely high performing servers, this number can of course be higher, but if better relative performance is the goal then lower is always better!

If however, your autoload size is higher than expected, then we need to identify where it’s coming from with the following command:

wp db query "SELECT option_name, LENGTH(option_value)/1048576 AS "Size" FROM $(wp config get table_prefix)options WHERE autoload='yes' ORDER BY "Size" DESC LIMIT 25 ;"

In plain language, all this command does is find the 25 largest rows in an options table that are being autoloaded. Here’s an example of what it looks like after execution:

At this stage, you need to get a little savvy with determining which autoload data is actually required. For example, I didn’t know what the “nice_template” option was related too, and I seen there were other rows prefixed with “nice_” too. I executed a grep -Rli nice_ command in my domain directory and quickly realised they were all related to my theme so I definitely don’t want to delete those. Looking down the list though, I did notice a few rows prefixed with wpseo_ which I suspected were related to the Yoast plugin. I don’t use Yoast personally, but as I often install popular plugins to familiarise myself with their setup, it’s possible that I didn’t uninstall Yoast correctly. As my grep command got no hits this a time, I turn to a quick search online to confirm and am now ready to remove those rows. To do so, I execute the following:

wp db query "DELETE FROM $(wp config get table_prefix)options WHERE option_name LIKE '%wpseo_%' ;"

NOTE: If you need to use this command to delete rows containing your own discovered string, just replace the string between the two % towards the end of the query.

Now that all rows that include “wpseo_” in the name are gone, I execute the same defrag command we saw earlier (wp db optimize) and test my site to ensure there are no adverse affects (remember you have your backup just in case!) and rinse/repeat with the rest of the autoload data.

Indexing

Ok, so now we’ve reduced the overall size of the options table, and we’ve cut out any unnecessary data that was being loaded with every request. Now there is just one more step to having a fantastically optimised options table and that’s indexing. Think of it like the contents of a recipe book. If there were none and the chef had to skip through every page to find the recipe for what you want to eat, you’d be hungry a lot longer than if the chefs recipe book had a contents page or index. Now imagine the recipe book has millions of pages just like your database and you’ll begin to get why indexing a MySQL database is so important!

First things, first. Make sure you’re options table doesn’t already have an index using the following command:

wp db query "SHOW INDEX FROM $(wp config get table_prefix)options ;"

And here’s what an index looks like in that list:

The “Column_name” and “Key_name” will almost always be autoload and the “Cardinality” value is usually very low. If you have this third row, then there’s no need to go further but if not, you read on!

If you are adding an index, first make sure it’s a good idea to do so, the general rule of thumb that I’ve found online is that the non autoloaded data should amount to between 60% and 80% of the total data before an index will help. You can compare these values using:

wp db query "SELECT COUNT(CASE WHEN autoload = 'yes' THEN 1 END) FROM $(wp config get table_prefix)options ;" ; wp db query "SELECT COUNT(CASE WHEN autoload = 'no' THEN 1 END) FROM $(wp config get table_prefix)options ;"

If you deem it necessary to add an index, you can do so with:

wp db query "CREATE INDEX autoload ON $(wp config get table_prefix)options(autoload, option_name) ;"

And you should you need to delete it for any reason;

wp db query "DROP INDEX autoload ON $(wp config get table_prefix)options ;"

Now that you’re options table has been optimised, your /wp-admin page should be loading substantially faster, and the site as a whole should be loading faster. For e-commerce sites particularly and particularly large news sites, the postmeta table can also become incredibly bloated and slow down users who are trying to naturally browse products or articles.

The postmeta Table

Before we start, it’s important to understand what kind of information is stored in the postmeta table. When you create a new post, the content of that post and basic information like the authors, the publishing date etc. are store in the posts table. Any additional data though is stored in the postmeta table and this can range from SEO plugin additions like metaboxes, review plugins that tie the post to a specific product, or simple custom fields that your theme is creating in the background. Additionally, your media library content also has meta data which is stored in the postmeta table. In most circumstances, the post table size should always outweigh the postmeta table size (except for gallery sites) so if the postmeta is much larger, that’s a pretty good indication that there’s an avenue of optimisation available.

As you’ve just optimised your options table you should have already seen the overall size of your postmeta table but how do you dig deeper? Well, similarly to the options table you can create ordered lists and look for data that you may potentially be able to get rid of. As the structure of the postmeta table is a little different though, we need to get creative with how we find the data. Luckily, the mate_key column in postmeta uses a leading _ for hidden settings and no leading _ for user accessible settings which allows us to naturally separate things using two different commands:

wp db query "SELECT SUBSTRING_INDEX(meta_key, '_', 1) AS "Meta",(SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS "Size", COUNT(*) AS "Count" FROM $(wp config get table_prefix)postmeta WHERE meta_key NOT LIKE '\_%' GROUP BY "Meta" ORDER BY "Size" DESC ;"

wp db query "SELECT SUBSTRING_INDEX(meta_key, '_', 2) AS "Meta",(SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS "Size", COUNT(*) AS "Count" FROM $(wp config get table_prefix)postmeta WHERE meta_key LIKE '\_%' GROUP BY "Meta" ORDER BY "Size" DESC ;"

Without going into too much detail about how to structure MySQL commands, all the above commands actually do is to parse through the meta_key column looking for any rows starting without an underscore, bunch the ones with the same name together, tot up the size by MB, and list them in descending order. The second command does the same thing but for rows that do start with an underscore. Both of these commands can be pasted in to your WP-CLI without edits though so no need to dwell on this too much.

At this stage, we now need to discern which rows are useful and which aren’t. This can be a little tricky so if you’re not familiar with MySQL, the only thing I’d recommend you do is execute the following two commands from the WP-CLI;

wp db query "SELECT MAX(t1.post_id) AS "post_id", MAX(t2.post_date) AS "Date", SUBSTRING_INDEX(t1.meta_key, '_', 1) AS "Meta", (SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS "Size", COUNT(*) AS "Count" FROM $(wp config get table_prefix)postmeta AS t1 JOIN $(wp config get table_prefix)posts AS t2 ON t1.post_id = t2.ID WHERE meta_key NOT LIKE '\_%' GROUP BY "Meta" ORDER BY "Date" DESC ;"

wp db query "SELECT MAX(t1.post_id) AS "post_id", MAX(t2.post_date) AS "Date", SUBSTRING_INDEX(t1.meta_key, '_', 2) AS "Meta", (SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS "Size", COUNT(*) AS "Count" FROM $(wp config get table_prefix)postmeta AS t1 JOIN $(wp config get table_prefix)posts AS t2 ON t1.post_id = t2.ID WHERE meta_key LIKE '\_%' GROUP BY "Meta" ORDER BY "Date" DESC ;"

These will print two tables (one with a leading _ and one without) which displays meta_key string, the size it takes up in the database, the amount of rows there are containing it and importantly, the date and post_id of the last time it was used. Here’s an example table;

In this particular case, the postmeta table is actually pretty well optimised but I can use this to identify rows I no longer need based on their meta_key value. Yoast and Imagify are quite recognisable and if I search online for “ez-toc” and “bbp”, I now know they are related to Easy Table of Contens and BBPress respectively, and I can choose to delete them with:

wp db query "DELETE FROM $(wp config get table_prefix)options WHERE option_name LIKE '%wpseo_%' ;"

NOTE: If you need to use this command to delete rows containing your own discovered string, just replace the string between the two % towards the end of the query.

The last thing we need to get rid of in postmeta is the meta data that is no longer associated with a post. Not every WordPress user will delete posts but even if you have no memory of doing so, it’s still a good idea to execute the following command for good measure:

wp db query "SELECT * FROM $(wp config get table_prefix)postmeta pm LEFT JOIN $(wp config get table_prefix)posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL ; DELETE pm FROM $(wp config get table_prefix)postmeta pm LEFT JOIN $(wp config get table_prefix)posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL ;"

This will simply look for any metadata associated with a post ID which has been deleted and remove it after printing it’s findings first.

As always, once I’m done deleting things, I run a quick wp db optimize, check the table size and make sure I don’t need to revert to my backup just in case!

The commentmeta Table

The last table which often becomes obscenely bloated is the commentmeta table. Like postmeta, this table can easily become cluttered over time with metadata for comments which no longer exist. Particularly if they have been removed by spam detection plugins like Akismet.

To list the rows being deleted before removing them completely, use the following command;

wp db query "SELECT * FROM $(wp config get table_prefix)commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM $(wp config get table_prefix)comments) ; DELETE FROM $(wp config get table_prefix)commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM $(wp config get table_prefix)comments) ;"

You can also delete all the Akismet meta data with the next command, although, by default this is done every 15 days so it may not be necessary;

wp db query "DELETE FROM $(wp config get table_prefix)commentmeta WHERE option_name LIKE '%akismet%' ;"

And as always, execute a quick wp db optimize to defrag the tables before testing to make sure you don’t need your backup!

Post Revisions

WordPress Post Revisions are sometimes understood to be Drafts but they are actually autosaves and old versionsawk ‘{print $1}’ /home/*/logs/*/http.*/access.log | sort | uniq -c |sort -n of all draft and published posts which are listed in the Document settings on the right hand side of your editor. WordPress limits the revision count to 10 by default, but depending on how you use the site, these can really stack up. For example, if you have 100 posts, and each one has 10 revisions, you’re database effectively has 1100 different posts in there which is less than ideal! Luckily there are two very simple steps to take to get back on track.

Firstly, you can get a quick count of the amount of revisions on your site with :

wp post list --post_type='revision' --format=ids | wc -w

Next, you can delete the existing revisions with:

wp post delete $(wp post list --post_type='revision' --format=ids) --force --quiet

And then finally, you can add the following directive to your wp-config.php file:

define('WP_POST_REVISIONS', 1);

This will limit the revisions count to 1 which still allows you an autosave point juuuuuuust in case! When adding anything to your wp-config.php, make sure you do so above the line that reads /* That's all, stop editing! Happy bloggin. */ but apart from that, there’s not much else to worry about.