WordPress Database Optimisation – WPQuickies

In this lunchtime #WPQuickies, I’m looking at ways to optimise your WordPress database.

A few years ago, I performed a website audit for a WordPress WooCommerce store.

They were on their third hosting company and couldn’t understand why their website was performing poorly.

A quick look at their database was all I needed to figure out why their website was so slow.

They had thousands of entries in their wp_posts table, but only a handful of published blog posts and the wp_options table were almost 2Gb.

They also had a logging plugin that was writing every WordPress event to the database, and those database tables were also over 1Gb in size.

After I had optimised their database by removing post revisions, orphaned posts and 10’s of thousands of expired transients, their website speed increased from a sluggish 12 seconds to 1.2 seconds load time for their main shop page.

Why am I telling you this story?

Database optimisation is a vital component of website maintenance to help your site’s overall performance.

WordPress Core Database Tables

If you install a fresh WordPress installation, the following eleven WordPress core database tables are created.

  • wp_commentmeta – Stores meta information for comments.
  • wp_comments – Stores comments.
  • wp_links – Creates and stores links menu (links feature sunsetted in v3.5). 
  • wp_options – Stores options set in the admin settings area.
  • wp_postmeta – Stores meta information for posts.
  • wp_posts – Stores data for posts, pages, and other custom post types.
  • wp_terms – Stores a list of tags and categories for all posts and links.
  • wp_term_relationships – Stores the relationship between a post and its category and tag and between links and link categories.
  • wp_term_taxonomy – Stores a taxonomy label in the wp_terms table. Taxonomy refers to a category, link, or tag.
  • wp_usermeta – Stores meta-information about users
  • wp_users – Stores a list of users.

Here’s a more detailed description of the WordPress core database tables, their schemas and relationships.

Why Should I Optimise My WordPress Database?

The WordPress database stores most of your website’s content, so as your website grows, so does your database’s size.

The bigger your database is, the slower it will take search functions to find the required data, and that will start to slow down your website’s overall performance.

What Data Fills Up A WordPress Core Database?

The usual offenders for quickly increasing the size of your WordPress core database tables are

  • Post Revisions
  • Spam Comments
  • Orphaned Post Metadata
  • Expired Transients
  • Trash

How to Check The Size of Your WordPress Database Tables

The best way to check the database tables sizes for your WordPress site is to use the phpMyAdmin database tool.

Most hosting providers will provide a link to this tool on their hosting control panels, such as cPanel or Plesk.

Look for the phpMyAdmin icon.

If your web host does not have a control panel or the phpMyAdmin tool, you can install and active and phpMyAdmin plugin from https://wordpress.org/plugins/wp-phpmyadmin-extension/.

Select your database name from the LHS navigation menu to display all the tables.

You can sort columns by clicking on their headings.

Sort the “Rows” column to see the tables with the most rows in them – these are usually the most oversized tables.

You can also sort the “Size” column, displaying the tables with the most extensive data in them.

The last column shows the database overhead per table.

When to Optimise Your WordPress Database?

I suggest performing these optimisation steps every month, so you keep on top of the task.

If you have a busy website, especially if you have users logging in or buying products or watching membership videos, I would recommend performing these tasks during non-peak activity.

Some database optimisations can remove items from shopping carts or other temporary caching issues.

How to Optimise The WordPress Core Database

I recommend using the WP-Optimize plugin from https://wordpress.org/plugins/wp-optimize/.

https://wordpress.org/plugins/wp-optimize/

I’ll be using this plugin in all the optimisation examples in this post.

Always make sure you have a current backup of your database before performing any of these optimisation tasks.

Some of the WP-Optimize tasks have a warning icon next to them to indicate that the job is database intensive and could result in corruption if the database is trying to shut down during the task.

Database corruption is a rare occurrence but can happen, which is why you need to take a database backup before attempting any of these tasks.

Note that all the functions WP Optimize offers, can also be performed by running SQL statements in phpMyAdmin, if you need that additional flexibility.

Optimising Database Table Overhead

Database table overhead is space left in a database table when data has been removed or updated.  

Recover overhead to decrease the overall size of the database.

From the WordPress admin menu, navigate to WP-Optimize > Database. 

In the “Optimise database tables” row, click on the “Run optimisation” button to recover the whole database’s table overhead.

You can perform the same task using phpMyAdmin if that’s your preference.

Select the tables you want to optimise – those with a table overhead in the last column.

Underneath all the tables, click on the dropdown box “With selected…” and choose the “Optimize table” option.

The phpMyAdmin tool will immediately start to optimise the selected database tables.

Depending on which storage engine you are using for each table (MyISAM or InnoDB), you may get some warning notifications during the process.

Technically only the older MyISAM storage engine supports table optimisation. 

However, for the newer InnoDB storage engine (which you should be using), phpMyAdmin will create an empty table and copy the selected table’s content over to the empty one, thus removing any table overhead.

Optimising Post Revisions

By default, WordPress keeps an unlimited number of post revisions (changes).

Post revisions are helpful if you need to undo some content you just saved, but you don’t need to keep post revisions from five years ago!

You can limit the number of post revisions that WordPress keeps by adding the following line to your theme’s functions.php file.

define( 'WP_POST_REVISIONS', 3 );

Change the number of revisions to whatever you are comfortable with storing.

Deleting Old Post Revisions From the Database

From the WordPress admin menu, navigate to WP-Optimize > Database. 

Click on the “Run Optimization” button for the “Clean all post revisions” option.

Deleting Post Revisions Using SQL

In phpMyAdmin, in the SQL tab, run the following SQL statement to delete all post revisions from the database.

DELETE FROM wp_posts WHERE post_type = "revision";

Spam comments

If you have a popular blog, you will get a lot of spam comments.

You should monitor your spam comments and delete them regularly.

Deleting Spam Comments

WP-Optimise has a couple of options for dealing with comments.

You can delete spam and trashed comments separately from deleting unapproved comments.

Of course, if you delete all unapproved comments, you may be deleting some genuine ones too, but this option can be handy if you have not been keeping up with comment moderation and you have hundreds or thousands of them.

Deleting Spam Comments Using SQL

In phpMyAdmin, in the SQL tab, run the following SQL statement to delete all post revisions from the database.

 DELETE FROM wp_comments WHERE comment_approved = "spam"
 If you need to delete all pending comments using SQL, run this command.
 DELETE FROM wp_comments WHERE comment_approved = "0" 

Orphaned Post Metadata

WordPress does a pretty good job of tidying up after itself when you delete a post, but things can and do go wrong.

Sometimes post metadata can get left in the database when the parent post is deleted.  

The left-behind metadata is called orphaned metadata.

Deleting Orphaned Metadata

WP-Optimise has three options for cleaning post metadata.

The “Clean post meta data” option will tidy up orphaned post metadata.

The “Clean comment meta data” option will perform the same optimisation but for comment metadata rather than post metadata.

The “Clean orphaned relationship data” option will remove any orphaned taxonomy data.

Deleting Orphaned Metadata Using SQL

In phpMyAdmin, in the SQL tab, run the following SQL statement to delete all post revisions from the database.

Orphaned Post Metadata
 DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL
 Orphaned Comment Metadata
 DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)
 Orphaned Term Metadata
 DELETE FROM wp_term_relationships WHERE term_taxonomy_id=1 AND object_id NOT IN (SELECT id FROM wp_posts) 

Expired Transients

Transients are a type of temporary data caching system used by plugin and theme developers and the WordPress core app.

When a transient expires, WordPress should delete it from the database. However, sometimes that process fails.

The WooCommerce and LearnDash plugins use many transients, and they can quickly lead to database bloat.

Deleting Expired Transients

Use WP-Optimise’s “Remove expired transient options” option to delete all expired transients from the database.

You can see that the plugin has found two expired transients out of a total of 31.

Please be careful not to delete all transients (the checkbox underneath) unless you need to.

Deleting all transients will cause issues with plugins and themes that rely on that data for caching purposes.

Trash

By default, when you delete a post using the WordPress UI, it will sit in the trash for 30-days, giving you enough time to perform a restore.

Again, sometimes this process can fail if, for example, the WP Cron is not working correctly.

Deleting Trash Posts

If you want to use the WordPress UI to do this, navigate to your Post and Pages views, then click on the “Trash” link and the “Empty Trash” button.

Using the Empty Trash button completely removes the trashed posts from the database.

However, it’s often more convenient to do this using the WP-Optimise plugin.

Navigate to WP-Optimise > Database and click on the “Run optimisation” button in the “Clean all trashed posts” row.

Auto-Draft Posts

For each draft you are working on, WordPress automatically saves a temporary revision. 

This type of revision is called auto-save.

WordPress stores an auto-save every 60 seconds. 

It also automatically replaces the older version with the new auto-save.

You can’t turn off auto-draft posts, and WordPress only saves one per draft post.

WP-Optimise can also delete any auto-draft posts.

Finding Inactive Database Tables

The WP-Optimize plugin has a handy setting that can find tables currently not used by any theme or plugin.

Navigate to WP-Optimise > Database, then click on the “Tables” tab.

This view lists all the tables in your WordPress database, highlighting tables not being used with the red word “inactive”.

This view can be a great way of finding old tables left behind by old plugins you have removed.

Warning: If you have deactivated a plugin temporarily and plan to reactivate it in the future, don’t delete those tables; otherwise, you will lose all your data.

Other Plugins

Other database optimisation plugins you can use are.

WP-Sweep (free)

WP Rocket (paid)

https://wpastra.com/go/wp-rocket

Autoloaded Data

When the WordPress app initialised itself on each page refresh, it automatically loads in some data from the wp_options table in the database for faster processing.

The autoloaded data can grow significantly over time, and not all plugins and themes will remove autoloaded data when being uninstalled.

If you open phpMyAdmin and navigate to the wp_options table in your WordPress database, you will see the last column, “autoload”.

The autoload column is either yes or no, so you can quickly see all the automatically loaded data.

To see how much data is being loaded by WordPress automatically, we have to perform a SQL query.

In phpMyAdmin, with your WordPress database selected, navigate to the “SQL” tab and copy in the following SQL statements.

Note: if your database has a different table prefix than “wp_” replace it in the query.

Click on the “Go” button to run the SQL.

Here’s the output from the SQL for my local database.  Your data will be different.

The first row, “autoloaded data in KiB”, specified the size in kilobytes of all the autoloaded data, in this case, 892K (0.82Mb).

That’s not too bad, but this is only a local test site, not a live production site.

The second row, “autoloaded data count”, tells you the number of items loaded, in this case, 389.

The next ten rows are the biggest option names in descending order, in this case, the option “e3d5414072f6eff914832a310ccbd95der” is the largest data item at 621908 bytes or 607K (0.6Mb).

I don’t know what that option name is or in what plugin or theme it belongs.

You or your developer will need to perform an investigation to track where those options are set if you want to figure out whether or not you can safely remove it from being automatically loaded in.

To set the autoload option to “no” for any option, you can use the following SQL for each option.

UPDATE wp_options SET autoload='no' WHERE option_name='option_name';

Replace “option_name” with the option’s actual name from the database table you can see in phpMyAdmin.

If you have removed any options, you need to build the “autoloadindex” in the database to make WordPress load the new values.

Run this SQL query in phpMyAdmin.

CREATE INDEX autoloadindex ON wp_options(autoload, option_name);

FAQs About WordPress Database Optimization

Q: Will it break my website?

Make sure you have regular backups in place.

Running these database optimisation tasks will not break your website.

Some tasks are intensive and may cause your website to slow down while being performed.

Q: Will it speed up my website?

That depends on how much the database could be optimised.

In general, yes, any optimisation will lead to an overall increase in website speed.

However, you will see a significant speed increase when large amounts of redundant data are removed from the database.

Conclusion

Keeping your WordPress database optimised will improve the database performance, leading to a faster website.

Use a plugin like WP-Optimize to perform all these tasks at a click of a button or schedule them to run regularly.

#WPQuickies

Join me every Thursday at 1 pm Sydney time for some more WPQuickies – WordPress tips and tricks in thirty minutes or less.

Broadcasting live on YouTube and Facebook.

Suggest a #WPQuickies Topic

If you have an WordPress topic you’d like to see explained in 30 mins or under, fill out the form below.

https://forms.gle/mMWCNd3L2cyDFBA57

Was this article helpful?
YesNo

Leave a Comment

Your email address will not be published. Required fields are marked *

Keep In Touch With My Weekly Blog Email

A once-per-week daily digest of my posts from the week. No sales, no spam and I will not share your details with anyone else.

Wil

Wil is a dad, WordPress consultant, WordPress developer, business coach and mentor. He co-organizes the WordPress Sydney meetup group and has been on the organising committee for WordCamp Sydney since 2014. He speaks at many special events and contributes to the WordPress open source project. His likes are chillies, craft beer and electrogravitics.

Leave a Comment

Your email address will not be published. Required fields are marked *