WordPress Database Optimization: Everything You Need To Know To Keep Your Site Clean & Fast in 2023

In this blog post, we’re going to look at a subject people don’t often talk about – how to optimize your WordPress database.

If you haven’t already developed the habit of keeping your SQL databases neat and tidy, then now is as good a time as ever to start.

We know this is the type of “task” site owners avoid because they’re terrified they’ll break something.

But as you’ll see in this tutorial, WordPress database optimization isn’t that tough at all when you’re using the WP-Optimize plugin.

It’s from the same people who created the popular Updraft Plus backup plugin, so that’s a good start.

You’ll be more than comfortable optimizing your database by the time you finish this tutorial.

Let’s get started.

How the WordPress Database Works

Your entire WordPress site is one big database, and more specifically a SQL database.

And just like any other DB you’ve ever used, this one is made up of multiple data tables.

Each major function of your WordPress site has its own table to keep things moving along smoothly.

As of WordPress 4.9, each install is made up of 12 separate tables, as illustrated below:

Why You Should Optimize Your WP Database

A database…contains data.

And the more information you add to it, the larger it will become.

Pretty much every change you make to your WordPress site increases the size of your SQL database file.

That includes installing and removing plugins, adding themes, comments (yes, including spam), post revisions, draft posts, etc.

There are two issues to consider here:

• Serving huge files impacts server performance
• Web hosts usually have an upper limit on database file sizes

So, not only will a huge WordPress database slow your site down, but your web host will eventually either charge you more for hosting it or ask you to find a new home for your site.

Some hosting companies will claim that the size of your SQL database is only limited by how much free disk space you have.

But watch what happens when your WordPress database hits 3GB and/or 1,000 tables – they’ll change their mind pretty quickly.

How can you tell how big your database is?

1. Login to the cPanel account for your site – this is usually domain.com/cpanel.
2. Scroll down until you find the MySQL Database icon and click on it.

3. Now locate the ‘Current Databases’ section.

Manual Optimization of Your Database

We wanted to cover this briefly to help prevent you from making mistakes you’ll have time to regret.

It is possible to manually optimize your WordPress database.

But it’s rarely a good idea.

For example, you can use the phpMyAdmin tool in cPanel to optimize each individual table of your WordPress database:

But you should avoid doing that for the simple reason that a mistake could result in you permanently trashing your data.

Or, in other words, you could accidentally delete all your pages and posts in less than 5 clicks.

And there’s very little chance of recovering them unless you have a recent backup.

So, please never attempt manual optimization – there’s really no need to go that far.

How to Use WP-Optimize to tidy up your database

Note

Please create a full backup of your WordPress install before following any of the steps in this tutorial.

We’d suggest using BackWPup or Updraft Plus to create your backup.

Note: You are risking the stability and operation of your site if you do not create a backup.

The WP-Optimize plugin is available free from the WP Repository, so simply install and activate it from there.

You’ll now have a menu item for the plugin, so click on ‘WP-Optimize’ to open its dashboard:

From here you can choose what optimizations you want to run,

or view information on individual tables.

And if you scroll down you’ll also see the current size of your WordPress database:

Note: Clicking on ‘WP-Optimize’ and then ‘Database’ brings you to this same screen.

You can also navigate to the different sections of this plugin using the quick access menu at the top of the screen:

Optimizing WordPress Tables

The first thing to watch out for is the option to ‘Optimize database tables’.

Basically, this plugin can’t optimize InnoDB tables, so you need to check a box to override this setting:

The reason you’re asked to do this is that the optimization process will completely rebuild InnoDB tables if you’re using an older version of MySQL.

And in our case, we are:

But this situation varies from one hosting provider to another, as you can see here:

You can choose between safe optimizations and the ones featuring an exclamation mark.

The optimizations with an exclamation mark carry the highest risk potential.

And we say potential because that’s all it is – something could go wrong.

What could potentially go wrong is running these optimizations could increase the load on your server, potentially causing it to reboot.

If that happens, then your database could be trashed.

But you can run the other optimizations whenever you wish by selecting them and then clicking ‘Run Optimization’.

• Optimize database tables – your database is made up of multiple tables, and each of these can contain redundant entries or junk data
• Clean all post revisions – these are the older versions of posts that you can swap back to in case you made a mistake.
• Clean all auto-draft posts – these are revisions of pages you edited that have been automatically saved but never used.
• Clean all trashed posts – posts that went to trash but weren’t automatically cleaned up after 30 days, which can happen.
• Remove spam and trashed comments– all those comments that you thought you deleted are still there, so they need to be erased to save space.
• Removed unapproved comments – you can use this to wipe all unapproved comments, which can be a real time-saver if you get a lot of spam.

It’s a good idea to take your time going through these optimizations, testing them one at a time.

You should also test that your site still responds as you’d expect after running an optimization.

Image Optimization

Another nice feature to help optimize your site and has an indirect benefit for database size.

This feature could also potentially replace any other image compression plugin you currently use.

After all, why have two active plugins when one will perform multiple tasks?

You can then deactivate and remove the one you don’t need, freeing up space inside your database.

Image optimization is automatic once you turn it on with ‘Automatically compress newly-added images:

And you can also choose what level of compression to use on each image:

• Prioritize maximum compression – small file size, lowest image quality
• Prioritize attention to detail – larger image size, higher image quality
• Custom – use a slider to choose the desired compression level.

The plugin defaults to ‘Custom’ with the slider set in the middle, so only adjust it if you’re not happy with the image file sizes you get.

Under ‘Advanced options’ you’ll find the compression service currently used to shrink your images:

We only tested using reSmush.it during our review, so we didn’t change any of the default settings.

Note: EXIF data is the identifying data stored within digital images, especially from digital cameras.

While there’s nothing wrong with the above image optimization features, we only use ShortPixel on our sites.

It’s fast, effective and very affordable – you can even get a free ShortPixel account to bring it for a test drive.

WP-Optimize Settings

From this screen you can enable the admin bar for the plugin or choose how long you should store data for:

As for how much data you should preserve, this will come down to how long you need to keep post revisions, unapproved blog comments, draft posts, etc.

So, adjust this setting to whatever works best for your personal circumstances.

But two weeks should be more than enough for most site owners.

You can also schedule how often your database should be cleaned and optimized:

This is in Beta right now, so we tend to keep away from features like this until they’re in production.

That being said, being able to tweak exactly what you clean from your database on a regular basis is a nice touch.

And finally you can choose to enable or disable trackbacks and comments on all previous posts:

We’ve disabled trackbacks here because…well…has anyone ever used them?

You can also disable future comments on any existing blog posts from this screen.

And that’s all there is to the different settings for WP-Optimize.

Conclusion

So, as you can see, optimizing your WordPress database for better performance isn’t nearly as technical as you might have thought.

Once you have a working backup of your site, and then take your time going through our WP-Optimize tutorial, you have nothing to worry about.

Did we trip up and miss something?

If so, let us know in the comments below, and don’t forget to share this content on your social profiles if you found it useful.