Today we will learn how to optimize WordPress Database. In this blog post, we’re going to look at a topic that people don’t often talk about – how to optimize your WordPress database. If you haven’t already made it a habit to keep your SQL databases clean, now is as good a time as any to start.
We know this is a “task” site owners avoid because they’re afraid they’ll break something. But as you’ll see in this tutorial, WordPress database optimization isn’t difficult at all when you’re using the WP-Optimize plugin.
This is from the same people who created the popular Updraft Plus backup plugin, so it’s a good start. You will feel comfortable optimizing your database by the time you finish this tutorial.
Let’s begin.
How the WordPress database works
Your entire WordPress site is a large database, and more specifically an SQL database. And just like any other DB you’ve used, it’s made up of multiple data tables.
Every major function of your WordPress site has its own table to keep things running smoothly.
As of WordPress, each install consists 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 bigger it gets.
Every change to your WordPress site increases your SQL database file size.
This includes installing and removing plugins, adding themes, commenting (yes, including spam), editing posts, drafting posts, etc.
There are two things to consider here:
- Serving huge files affects server performance
- Web hosts usually have an upper limit on the size of database files
So, not only will a huge WordPress database slow down your site, but your web host will eventually charge you more to host it or ask you to find a new home for your site.
Some hosting companies will claim that your SQL database size 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 minds pretty quickly.
How to find out how big your database is?
- Login to the cPanel account for your site – this is typically domain.com/cpanel.
- Scroll down until you find the MySQL Database icon and click it.
Now locate the ‘Current Database’ section
Now locate the ‘Current Database’ section
Manual optimization of your database
We wanted to cover it briefly to prevent you from making mistakes you’ll later regret.
Manually optimizing your WordPress database is possible.
But this is rarely a good idea.
For example, you can use the phpMyAdmin tool in cPanel to optimize every single table in your WordPress database:
But you should avoid doing this for the simple reason that one mistake can permanently trash your data. Or, in other words, you can accidentally delete all your pages and posts in less than 5 clicks.
And unless you have a recent backup, the chances of restoring them are slim. So, please never try manual optimization – there’s really no need to go that far.
How to use WP-Optimize to clean up your database
Please make a full backup of your WordPress website install before following any of the steps in this tutorial. We recommend using BackWPup or Updraft Plus to make your backups.
Note: If you do not create a backup, you are putting your site’s stability and operation at risk. The WP-Optimize plugin is freely available from the WP repository, so simply install and activate it from there.
You will now have a menu item for the plugin, so click on ‘WP-Optimize’ to open its dashboard:
From here you can choose which optimizations you want to run,
or view information on individual tables.
Optimizing WordPress Tables
The first thing to look out for is the ‘Optimize Database Table’ option.
Basically, this plugin can’t optimize InnoDB tables, so you have to check a box to override this setting:
The reason you are asked to do this is that if you are using an older version of MySQL, the optimization process will completely rebuild the InnoDB tables.
And in our case, we are:
But this situation varies from one hosting provider to the other, you can see here:
You can choose between safe optimizations and those containing an exclamation mark.
Optimization with an exclamation point carries the highest risk potential.
And we say potentially because that’s all – something can go wrong.
Running these optimizations can increase the load on your server, potentially causing it to reboot if something goes wrong.
If so, your database may be trashed.
But you can run other optimizations whenever you want by selecting them and then clicking ‘Run Optimization’.
Optimize Database Tables – Your database consists of multiple tables, and each of them may contain redundant entries or junk data.
Clear All Post Revisions – These are old versions of posts that you can swap back in if you’ve made a mistake
Clear All Auto-Draft Posts – These are revisions to pages you’ve edited that have been automatically saved but never used.
Clean up all trashed posts – Posts that went to the trash but weren’t automatically cleaned up after 30 days, which can happen.
Remove spam and trashed comments – Comments you thought you deleted are still there, so delete them to save space.
Removed unauthorized comments – You can remove all unauthorized comments using this, 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, checking them out one at a time.
You should also check that your site still responds the way you expect after running an optimization.
Image optimization
Another nice feature to help you optimize your site and has an indirect benefit for database size. This feature can potentially replace any other image compression plugin you may be currently using.
After all, why have two active plugins when one can perform multiple tasks?
You can then deactivate and remove any you don’t need, freeing up space inside your database.
‘Compress newly-added images automatically:
And you can also choose which level of compression to use on each image:
- Prioritize maximum compression – small file size, and minimum image quality
- Prioritize attention need to detail – larger image size, higher image quality
- Custom – Use a slider to select the desired compression level
The plugin defaults to ‘Custom’ with the slider set in the middle, so adjust it only if you’re not happy with the image file sizes you get.
Under ‘Advanced options’ you’ll find the compression service currently used to compress your images:
We only tested using reSmush. During our review, we, therefore, did not change any default settings. Note: EXIF data is identifying data stored in digital images, especially from digital cameras.
While there is nothing wrong with the image optimization features above, we only use short pixels on our site.
It’s fast, efficient, and very affordable – you can even bring it for a test drive with a free ShortPixel account.
WP-Optimize Settings
From this screen you can enable the admin bar for the plugin or choose how long to save your data:
How much data you should store, will come down to how long you should keep post revisions, unapproved blog comments, draft posts, etc. So, adjust this setting to what works best for your personal situation.
But two weeks should be enough for most site owners. You can determine how often your database should be cleaned and optimized:
It’s in beta right now, so we tend to stay away from these types of features until they’re in production.
That being said, being able to tweak exactly what you purge from your database on a regular basis is a nice touch.
And finally, you can choose to enable or disable trackback and comments to all previous posts:
We’ve disabled trackbacks here because…well…does anyone ever use them?
You can disable future comments on any existing blog post from this screen.
And that’s it for the various settings for WP-Optimize.
Conclusion
So, as you can see, optimizing your WordPress database for better performance isn’t nearly as technical as you thought. Once you have a working backup of your site, and then take your time with our WP-optimize tutorial, you have nothing to worry about.
