If you are noticing problems with your site running slower than usual, and you want to optimise its speed, you should consider cleaning the WordPress database among other tasks.
The WP database is a fundamental component of the website, because this is where all important information is stored, such as posts, pages, comments, site, theme and plugin settings. Just to give a few examples.
Over time, this very overcrowding also fills the database with files that have no use, but which occupy a lot of space.
These include drafts, chat transcripts, post revisions, spam comments, superfluous data, documents in the recycle bin, etc. All this useless stuff ends up burdens and slows down the site, causing performance problems that affect the browsing experience.
The operation that can be done to solve this, therefore, is a cleaning up of what is not needed. And in this article we will see how to do this, how to clean up the WordPress database using two different methods: taking advantage of the (free) plugin automation or the manual cleaning procedures in the phpMyAdmin panel.
In addition, at the end of the article you will find tips on how to prevent the problem of too much accumulation in the WordPress db.
Why clean the WordPress database
This is one of those operations that may seem optional, because we are inclined to give more importance to what we see, to what is before our eyes. But even if the database is ‘invisible’, it is actually the skeleton of the site.
Here are some of the advantages of cleaning up the WordPress database on a regular basis:.
- Improves site speed and performance
- Saving server space
- Faster and smaller backups
- More security: a clean database contains fewer vulnerabilities that can be exploited by cyber criminals
- Smoother navigation = more opportunities for conversion (whatever your goals are).
You can check the performance of your site with specific tools such as Google Page Speed Insights or GTMetrix to identify any database-related problems.
Make a good backup first!
First of all, we make a full backup of the site, which can be done manually, through phpMyAdmin, or by using plugins such as UpdraftPlus, BackupBuddy or Jetpack VaultPress Backup.
This step is highly recommended in order not to lose important data during the db cleaning procedure.
How to clean the WordPress database with plugins (simple and automatic procedure)
If you prefer not to put your hands in the code, there are numerous plugins that can help you optimise the WordPress database.
Among them, you can consider some that do a great job, including the possibility of scheduling operations over time to automate the process with regular cleaning of post revisions, removal of spam comments, optimisation of database tables.
Plugins to optimise the WordPress database.
- WP-Optimize
- WP-Sweep
- Advanced Database Cleaner
- WP-DBManager
- WP Reset.
Let us see how to configure WP-Optimise and WP-Sweep.
How to optimise your WordPress db with WP-Sweep
- Download and install the plugin WP-Sweep.
- Activate the plugin
- Go to ‘Tools’ from where you can select the operations to be performed.
The plugin analyses the entire WordPress database, from top to bottom, showing a report of the content to be deleted.
The report is divided into the various sections between posts, comments, users, options, etc.
The deletion of unnecessary elements can be done individually, or en masse, with a click on the button at the end of the page that cleans up the entire WordPress database.
The time required to finish the deletion operation depends on the size of the database. Once the procedure is finished, WP-Sweep gives you a report on the improvements achieved.
The plugin eliminates: .
- Reviews
- Automatic drafts
- Spam / deleted / unapproved comments
- Orphan post meta / Orphan comment meta / Orphan user meta / Orphan term meta
- Relationships between orphan terms
- Terms not used
- Duplicate post meta / Duplicate comment meta / Duplicate user meta / Duplicate term meta
- Optimises database tables
- Cache in post meta
How to optimise your WordPress db with WP-Optimize
Another excellent plugin is WP-Optimise, a combination of tools for database cleaning, site caching, and image compression. Like the previous one, the plugin is free with some premium options.
How to proceed:.
1. Download, install and activate the plugin
2. Go to WP-Optimise
3. Select Database in the WordPress dashboard
4. Under ‘Optimisations’ select the database elements to be cleaned up
5. Select ‘Perform optimisation’ next to each action. Or check the box next to all and click ‘Perform all selected optimisations’ at the top of the page.
To delete specific tables, open the ‘Tables’ tab and click on ‘Remove’ next to the rows to be deleted.
If exclamation marks appear next to certain actions, this means that in this case database optimisation is more risky and could damage data.
WP-Optimise can eliminate:.
- Revisions of posts
- Self-produced messages
- Binned messages
- Spam and trashed comments
- Non-approved comments
- Transitional options (temporary copies of your data)
- Pingbacks and trackbacks
- Metadata of posts and comments
- Orphan Report Data
- Optimisation of database tables to remove unnecessary data.
We set up automatic optimisations of the WordPress DB
To set up automatic database optimisations, go to the “Settings”tab and schedule automatic database optimisations by choosing which data are to be deleted during this process.
From the ‘Settings’ menu, you can also select the number of weeks, data and post revisions to keep. Click ‘Save Settings’ at the bottom of the page when you have finished with the options.
How to clean the WordPress database manually
Alternatively, the WordPress database can be cleaned manually with phpMyAdmin from cPanel in the dashboard of the hosting provider.
The procedure is more complex but allows a more complete control and can avoid adding another plugin to the already numerous list. And as we know, plugins can also be a cause of site slowdowns.
The shared instructions may change depending on the hosting provider, but they are mostly the same.
- Log in to your hosting account
- Open cPanel and enter the phpMyAdmin area
- Select the Database of your WordPress site
You should now see a list of all tables in the WordPress database.
You can either choose the ones to be cleaned or scroll to the end of the page and select ‘Check All’. Then click on ‘Optimise Table’ from the drop-down menu.
After a few seconds you should see a message advising you of the successful action.
How to avoid flooding the WordPress database
Prevention is always better than cure… and this is also true with regard to the WordPress database.
Here, then, are a few tricks.
Reduce the number of saved revisions.
By default, WordPress saves revisions of posts and pages. This function allows you to review different versions of content and return to a previous copy.
But unfortunately, saving too many revisions can flood the WordPress database. So, it is best to reduce the number.
Here is how to do it with a simple code addition to the wp-config.php file. To find the file, access the site via an FTP (File Transfer Protocol) client, not without first making a complete backup of the site.
- Open wp-config.php and add this code to the file:.
define( ‘WP_POST_REVISIONS’, X );.
replacing ‘X’ with the number of revisions of the post you want WordPress to save (e.g. ‘2’).
It is also possible to disable post revisions altogether, but this is not a good idea… in the future this function may always be needed.
Prevent spam comments.
Spam comments can do different kinds of damage: contain malicious links, degrade the site, take up space in the WordPress database.
It is true that WordPress deletes spam comments after 30 days, but if the site generates a lot of traffic, the flow of comments may rise like the high tide.
That is why it is worth using an anti-spam plugin to automatically filter out malicious comments. The Jetpack Anti-Spam plugin removes spam from comments and forms without you having to get your hands on it every time.
It is also possible to delete spam comments from the database by using a plugin such as WP-Optimise (as we have seen above).
Alternatively, you can also do this manually via phpMyAdmin.
Open the WordPress database, click on the Comments option from the left-hand menu, which shows you a list of ‘elements’.
Open SQL and enter.
Cancella da wp_comments dove comment_approved = ‘spam’;
to delete all spam comments.
If your comments table has a different name, you must put that in place of ‘wp_comments’.
Click OK to complete the action.
Regularly empty the WordPress recycle bin.
All deleted files, including posts, pages, media files and comments, accumulate in the WordPress trash folder.
By default, WordPress empties the recycle bin after 30 days, but as with comments, if you often delete a lot of material, it may be useful to increase the frequency of bin cleaning.
This operation can also be configured by editing the wp-config.php file.
Open the file via FTP and add this code fragment:.
define( ‘EMPTY_TRASH_DAYS’, 5 ); // 7 days.
In this example, the basket is emptied after 7 days. Enter the number you think is most appropriate.
Deletes unused plugins and themes.
Themes and plugins take up a lot of space in the database. To remedy the problem, deactivate them if you are not currently using them, or delete them if you think you will never use them.
Deleting these components is also good from a security point of view. A hacker can use these deactivated files to inject malware.
Here’s how.
From the Plugins panel → Installed Plugins → click on Delete (after deactivating).
Repeat the same procedure for WordPress themes under Appearance → Themes.
Some WordPress plugins and themes leave their traces within the WordPress db, thus contributing to the accumulation of useless stuff.
So, this data must be removed manually using phpMyAdmin.
- Open the database in phpMyAdmin and click on the search bar.
- Type in the name of the newly deleted plugin to find any orphan tables in the database.
- Click on Delete to remove them from the database.
Disable Pingback and Trackback
These two names stand for the automatic notifications that arrive when other websites link to your content (and vice versa). Spammers use them to draw attention to their own content. But pingbacks can also start in connection with internal links you insert in your posts.
You can find them in the ‘Comments’ section of the WordPress dashboard.
The fact is that pingbacks and trackbacks also take up space in the database. So it is a good idea to disable them by going to Settings → Discussion. From there, uncheck the boxes next to notify any blogs linked to the post and Allow link notifications from other blogs (pingbacks and trackbacks) on new posts.
Save changes.
You can delete pingbacks and trackbacks from the WordPress database using a plugin such as WP-Optimise or via phpMyAdmin.
- Open the database with phpMyAdmin
- Click on the SQL tab
- Enter this code:
UPDATE wp_posts SET ping_status= “closed”;
Replace ‘wp-posts’ with the name of your database table. Click OK to delete the data.
Delete unused media files.
Images and videos are very useful resources that can make your content more engaging. But over time you may want to replace old resources with more current ones. Thus, obsolete and unused material will accumulate in the database and penalise the site.
Deleting these files from the Media Library is easy. Just go to Media → Library; click Delete. A plugin that simplifies the job by automating the cleaning of many media files is Media Cleaner.
