If your site has started running slower than usual and you want to get the speed back, cleaning the WordPress database belongs on the list of things to try.
The WP database is a core part of the site, because this is where all the important information is kept: posts, pages, comments, site settings, theme settings, plugin settings. To name a few.
As time passes, that same accumulation fills the database with material that serves no purpose at all but takes up a great deal of space.
Drafts, chat transcripts, post revisions, spam comments, leftover data, items in the bin, and so on. All that useless clutter ends up weighing the site down and slowing it, causing performance problems that visitors feel while browsing.
The fix, then, is a clear-out of what is not needed. In this article we look at how to do it, how to clean the WordPress database two different ways: with the automation of free plugins, or by hand from the phpMyAdmin panel.
At the end you will also find advice on preventing the problem of build-up in the WordPress database in the first place.
Why clean the WordPress database
This is one of those jobs that can look optional, because we naturally give more weight to what we can see. But even though the database is "invisible", it is in fact the skeleton of the site.
Some of the benefits of cleaning the WordPress database regularly:
- Site speed and performance improve
- You save space on the server
- Backups get faster and smaller
- Better security: a clean database holds fewer weak points for attackers to use
- Smoother browsing = more chance of converting (whatever your goals are)
You can check how your site performs with tools such as Google PageSpeed Insights or GTmetrix, to spot any problems connected to the database.
Take a proper backup first
Before anything else, make a full backup of the site. You can do it by hand through phpMyAdmin, or with plugins such as UpdraftPlus, BackupBuddy or Jetpack VaultPress Backup.
This step is strongly recommended so you do not lose important data during the clean-up.
How to clean the WordPress database with plugins (simple and automatic)
If you would rather not touch any code, there are plenty of plugins that can help you optimise the WordPress database.
Several of them do an excellent job, including the option to schedule the work over time so the process runs itself, with regular clearing of post revisions, removal of spam comments and optimisation of the database tables.
Plugins for optimising the WordPress database
- WP-Optimize
- WP-Sweep
- Advanced Database Cleaner
- WP-DBManager
- WP Reset
Let us look at how to set up WP-Optimize and WP-Sweep.
How to optimise the WordPress database with WP-Sweep
- Download and install the plugin
- Activate the plugin
- Go to "Tools", where you can pick the operations to run
The plugin scans the whole WordPress database, top to bottom, showing a report of the content it can remove. The report is split into sections: posts, comments, users, options and so on.
Useless items can be removed one at a time, or all at once, with a click on the button at the foot of the page that sweeps the entire WordPress database. How long the deletion takes depends on the size of the database. Once it finishes, WP-Sweep gives you a summary of what improved.
The plugin removes:
- Revisions
- Auto drafts
- Spam / deleted / unapproved comments
- Orphaned post meta / orphaned comment meta / orphaned user meta / orphaned term meta
- Orphaned term relationships
- Unused terms
- Duplicated post meta / duplicated comment meta / duplicated user meta / duplicated term meta
- Optimises the database tables
- Cache in post meta
How to optimise the WordPress database with WP-Optimize
Another very good plugin is WP-Optimize, a combination of tools for database clean-up, site caching and image compression. Like the previous one, the plugin is free with some premium options.
Here is how to go about it:
- Download, install and activate the plugin
- Go to WP-Optimize
- Select Database in the WordPress dashboard
- Under "Optimizations", select the database items to clear out
Click "Run optimization" next to each action. Or tick the box next to all of them and click "Run all selected optimizations" at the top of the page.
To remove specific tables, open the "Tables" tab and click "Remove" next to the rows you want gone.
If exclamation marks appear next to some actions, it means that in those cases the optimisation carries more risk and could damage data.
WP-Optimize can remove:
- Post revisions
- Auto-drafted posts
- Posts in the bin
- Spam and binned comments
- Unapproved comments
- Transient options (temporary copies of your data)
- Pingbacks and trackbacks
- Post and comment metadata
- Orphaned relationship data
- Optimisation of database tables to remove unnecessary data
Setting up automatic WordPress database optimisation
To schedule automatic database optimisation, go to the "Settings" tab and plan the automatic runs, choosing which data should be removed during the process.
The "Settings" menu also lets you choose the number of weeks, and how much data and how many post revisions to keep. Click "Save settings" at the foot of the page when you have finished with the options.
How to clean the WordPress database by hand
Alternatively, you can clean the WordPress database by hand with phpMyAdmin. The procedure is more involved but gives you fuller control, and it avoids adding yet another plugin to an already long list. As we know, plugins can themselves be a cause of a slow site.
The instructions below can vary depending on the hosting provider, but they are largely the same everywhere.
- Sign in to your hosting account
- Open the control panel and go into the phpMyAdmin area
- Select the database of your WordPress site
You should now see a list of all the tables in the WordPress database. You can pick the ones to clean, or scroll to the foot of the page and select "Check all". Then click "Optimize table" from the drop-down menu.
After a few seconds you should see a message confirming the action succeeded.
How to keep the WordPress database from clogging up
Prevention beats cure, and that holds for the WordPress database too.
Reduce the number of saved revisions
By default WordPress saves revisions of posts and pages. The feature lets you review different versions of your content and go back to an earlier copy.
Unfortunately, saving too many revisions can clog the WordPress database. So it is worth cutting the number down.
Here is how, with a small addition to the wp-config.php file. To find the file, connect to the site over FTP — and not before you have taken a full backup.
Open wp-config.php and add this to the file:
define( 'WP_POST_REVISIONS', X );
replacing "X" with the number of post revisions you want WordPress to keep (for example, "2"). You can also turn post revisions off entirely, but that is not a good idea: one day you will want the feature back.
Keep spam comments out
Spam comments do several kinds of damage: they can carry harmful links, they degrade the site, and they take up space in the WordPress database.
It is true that WordPress deletes spam comments after 30 days, but if the site gets a lot of traffic the flow of comments can rise like a tide. That is why it is worth using an anti-spam plugin to filter harmful comments automatically. The Jetpack Anti-Spam plugin removes spam from comments and forms without you having to step in each time.
You can also delete spam comments from the database using a plugin such as WP-Optimize, as we saw above.
Empty the WordPress bin regularly
Everything you delete — posts, pages, media files and comments — piles up in the WordPress bin.
By default WordPress empties the bin after 30 days, but as with comments, if you delete a lot of material often it can be worth emptying it more frequently.
This too can be configured by editing the wp-config.php file. Open it and add this snippet:
define( 'EMPTY_TRASH_DAYS', 5 ); // 5 days
In this example the bin is emptied after 5 days. Put in whatever number suits you.
Delete unused plugins and themes
Themes and plugins take up plenty of space in the database. To deal with it, deactivate the ones you are not using at the moment, or delete them if you think you never will.
Getting rid of these components is also good from a security point of view. An attacker can use deactivated files to inject malware.
Turn off pingbacks and trackbacks
Those two names refer to the automatic notifications that arrive when other websites link to your content, and the other way round. Spammers use them to draw attention to their own material. But pingbacks can also fire from internal links you put in your own posts.
You will find them in the "Comments" section of the WordPress dashboard.
The point is that pingbacks and trackbacks take up database space too. So it is worth disabling them from Settings → Discussion. There, untick the boxes for notifying any blogs linked from the post and for allowing link notifications from other blogs (pingbacks and trackbacks) on new posts.
Delete unused media files
Images and video are useful things that can make your content more engaging. But over time you may want to swap older assets for current ones. Outdated, unused material then builds up in the database and penalises the site. Go to Media → Library and click Delete. A plugin that makes the job easier by automating the clean-up of large numbers of media files is Media Cleaner.
