Have you been using WordPress for quite some time? If yes, you need to declutter your database to take care of all that useless data that has led to database bloating. This article shows you four ways to perform WordPress database cleanup so that your database runs more optimally.
Your website slows down considerably due to a bloated database. Imagine if the themes you deleted years ago still have their settings stored in your database. Your WordPress database stores all your content, including spam comments, old post revisions, the settings and tables of your old themes and unused plugins, and much more.
To free up your WordPress database space and make your site load faster, you need to declutter the useless storage in your database to experience higher efficiency.
Here are some of the advantages of a routine WordPress database cleanup:
- It’s an incredible way to ensure website integrity, as your site gets an edge through optimized performance.
- It leads to a reduction in:
○ Chance of loss of data
○ Chances of the site crashing
- It lowers storage waste.
- You get to rank higher on search engines.
- It ensures all users’ (both at the frontend & backend) experience a fast and efficient website since “speed does matter.”
We strongly recommend you create a complete backup of your website as a starting point for peace of mind. Do this before making any changes to your website via the ways to perform WordPress database cleanup mentioned in this article.
In this article, we don’t get into the nitty-gritty of backing-up methods. Yet, you should know that your site’s backup gives your effortless restoration power in case of a mishap while cleaning up. As a tip, if you’re looking for an efficient and easy way to backup your site, you may want to give a plugin like BlogVault a shot.
The Methods to Cleanup your WordPress Database
There are many techniques you can choose from when it comes to cleaning up your WordPress database. Using an SQL query is by far one of the most direct methods, yet it’s suitable for advanced users only. Alternatively, a professional can be hired to undertake the process for you, especially if you have a vast database and WooCommerce to deal with. More popularly, some incredible plugins help manage the task with considerable ease.
Our focus is on the most popular WordPress database cleanup plugins in this article since they are easy to use and adhere to a broader audience’s requirements. However, for the advanced users, as our last method, we have also mentioned how to optimize your WP database with phpMyAdmin by running SQL queries.
In this article, we have selected the most popular and easy to conduct ways to get the job of performing WordPress database cleanup done. Some incredible plugins like WP-Optimize, Advanced Database Cleaner, and WP-Sweep, among others, are tried and tested methods that will lead your site to function much faster and more efficiently. Let’s begin with these.
The WP-Optimize plugin is an incredible choice for a database optimization tool. Currently, its number of installations has exceeded 800,000, and there’s no stopping its popularity.
It not only cleans your database but also compresses images and caching pages of your database. These features of caching pages and compressing images help enhance the speed of your website through database optimization.
The database cleaning process happens on a schedule. Optimization is achieved through compacting tables and keeping the backups. All this, while WP-Optimize, also works to remove all the useless files.
This plugin comes with constant updates, so your site is always on track. It’s free to an extent, yet to unlock all its capabilities, you must go for the premium version. With the free version, you can avail of the main functionalities: database cleaning, site caching, and image compression. Also, the free version can be installed on any number of sites.
How Does WP-Optimize Work?
There are settings for ‘Optimizations‘ and ‘Tables.’ In the optimizations settings, you can leave aside unapproved comments and post revisions, and delete everything else. Alternatively, you may decide to optimize every item listed.
In the settings for ‘Tables,’ you would want to pay particular attention to those database tables that say “not installed” and are accompanied by a ‘Remove’ button next to them. You should remove these tables since they have been left behind by old plugins that you don’t use anymore.
We advise you to schedule ongoing database clean-ups through WP-Optimize. To select this option, go to WP-Optimize > Settings. The plugin lets you automate your database’s cleanup so that you can schedule it to happen as per your selection automatically. We recommend you fix it to occur at least once a week.
The limitation with WP-Optimize is that it may leave orphaned data after processing. This means, while it does a great job at the overall cleaning of the database, it leaves behind fragments of useless information still stored on the system. Another popular plugin, by the name of ‘WP-Sweep,’ is known to prevent better and eliminate orphaned data.
This is another free yet easy-to-use plugin that works wonders when it comes to WordPress database cleanups. It removes useless information piled up in your website’s database so that your site can function better. Things like copies of revisions that WP retains, deleted comments, spam elements, and much more are cleaned up.
Go to the Plugins area of your dashboard and click “Add New.” Next, install and activate “WP-Sweep.” Another way of locating the plug for installation is by using the search field.
Once WP-Sweep is installed, to start the cleanup process, go to the ‘Tools’ section and click on “Sweep. What will appear are the data types, number of entries, and information about the useless elements of the existing database. You will be able to decide the areas that need optimization.
Next, click on the “Details” button. Here you will see the entries you can delete as identified by WP-Clean.
Now, you may click the “Sweep” button next to the specific component that you wish to be cleaned up. Alternatively, you may get rid of all the entries in one go by scrolling down to “Sweep All” at the very bottom.
The WP-Sweep plugin cleans all the spammed, deleted, and unapproved comments and removes metadata, including term, comment, and user data. Moreover, this plugin does an incredible job of pinpointing and eliminating content and data stored in duplicate.
Always remember, it’s highly recommended you keep a backup of the database. If you end up deleting (sweeping clear) a necessary component, at least you’ll have the ability to restore it if needed.
3. Advanced Database Cleaner
The Advanced Database Cleaner tool is another incredibly easy to use plugin that lets you set your database cleanup routine frequency. It also enables you to decide which files you want to be excluded in the cleanup process.
You save time as the plugin has the capability of removing all unwanted background database items. It lets you schedule a regular cleanup routine so that your site ends up being optimized for speed and performance. A clunky database would be a thing of the past once you install and run this tool.
You can conveniently set it up for auto cleaning. As it performs the WordPress database cleanup, things like orphan data, old auto drafts, pingbacks, among other useless data components, are cleared up as per your preferences. There is also multi-site support, so it functions as a single solution for all your worries. The core plugin is free of cost, and there are premium options at reasonable prices.
4. Optimize Your WP Database With phpMyAdmin
This is the fourth and final method in this article. It’s the only method that doesn’t involve a plugin. If your server has a cPanel, phpMyAdmin is the most direct, best, and easiest way to perform WordPress database cleanup.
To start the process, simply login to cPanel for your site. Then click “phpMyAdmin” in the “Databases” section.
When in phpMyAdmin, you’ll notice your site’s databases will be listed on the left-hand side. You can choose by clicking on the one requiring a cleanup and then click on the “SQL” tab.
From the above image of the screen, you can see there is an SQL section in phpMyAdmin. This is the place you’ll enter the SQL commands and then hit “Go” to execute the commands.
We have used the default table prefix wp_. You should change them in the SQL commands to match your database.
Here’s how you manually undertake the process of deleting all the junk in your database using SQL commands:
- Remove the Old Plugin and Post Data
We first need to remove the leftover data from old plugins that are no longer installed. The post data is stored in the wp_postmeta table. Run the following query to delete the old plugin and post data:
DELETE FROM wp_postmeta WHERE meta_key = ‘META-KEY-NAME’;
You have to replace META-KEY-NAME with the value you desire to be cleared out in the above query.
- Remove Post Revisions
To delete all of the post revisions that may have accumulated over an extended period, run the following query:
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
WHERE a.post_type = ‘revision’
AND d.taxonomy != ‘link_category’;
- Remove Spam Comments
To remove all of the spam comments in a single go, run the following query:
DELETE FROM wp_comments WHERE comment_approved = ‘spam’;
- Remove Unapproved Comments
To bulk delete all unapproved comments, admins may run the following query:
DELETE from wp_comments WHERE comment_approved = ‘0’;
How to Optimize Tables with phpMyAdmin?
In phpMyAdmin, you can manually optimize all your tables without the help of any plugin.
Here’s how you do it:
In phpMyAdmin, go to the “Structure” tab and choose the database you desire to optimize and click on your selection. Scroll down to the bottom and click on “Check all,” and select “Optimize table” found in the adjoining dropbox.
Once optimized by phpMyAdmin, you will see the following message:
“Your SQL query has been executed successfully.”
As detailed above, an SQL query is the most direct method to perform a WordPress database cleanup, yet it’s suitable for advanced users only. If you are either a new or advanced WordPress user, it may be of further help to read any of our helpful tutorials, including the one on “How to Fix “ERR_TOO_MANY_REDIRECTS” on WordPress?”
We have also mentioned the more popular route for WP database cleanups, which is using plugins. We have mentioned three of them, but many other incredible plugins are either free or premium. These plugins are easy and efficient to use.
When it comes to the cleanup of your WP database, we would be delighted to hear which plugin or method you’ve had experience with. Use the comment section to enlighten us with your thoughts!
For further knowledge, also check out our article titled “5 Best WordPress Malware Removal Plugins Of 2020“.