There are two important components of a typical WordPress installation:
- WordPress files stored on your server
- WordPress database
It’s very essential for you to clean up your WordPress database and reduce its size from time to time.
Over time, your WordPress database has accumulated many redundant tables, unused records, and many entries which you can remove without affecting your website.
This is a must-do process for every WordPress user to keep the size of their database at a minimum and keep their WordPress blog loading quickly.
This will help reduce the load on your server and your WordPress performance will improve dramatically.
In this tutorial, I will take the example of ShoutMeLoud’s WordPress database which is 286.3 MB and drastically reduce it’s size.
You can follow this guide step-by-step and perform these tasks on your own blog.
Note: A few of the points require a little technical know-how, and if you find yourself questioning, feel free to ask in the comments or skip that step entirely.
Tutorial: How to Reduce WordPress Database Size
Step 1: Take WordPress Database Backup (IMPORTANT)
This is the most essential step.
In my case, I have used my hosting company’s backup feature (WPEngine backup) to take a complete backup of the database and all included files.
You can also use the WP-db manager plugin to take a backup of your database file. You can find a tutorial over here. I highly recommend the WP-db manager plugin because we will be needing to run a few SQL queries, empty, and drop database tables (all of which this plugin can do).
Step 2: Disable AND Delete Unused WordPress Plugins
Go through the active plugin list on your WordPress blog and disable those plugins which you are not using anymore. There might be plugins that you use once in a while, but I suggest you disable them now and re-install them when they’re needed again.
Our goal for today is to completely optimize the database size.
Only disabling unused plugins will not be of much help here, so make sure you also delete those unused plugins.
Speaking of useful plugins:
- Basic WordPress Plugins For Every Blog
- Must Have SEO WordPress Plugins
- List Of Plugins That I Use Here At ShoutMeLoud
Step 3: Delete All Spam Comments, Trash Posts, and Post Revisions
What ever comments you have in your spam and trash folders, delete them.
Similarly, delete all posts from the trash folder and delete all post revisions.
- You can use the WP-optimize plugin to get this done with one click.
Step 4: Find and Clean Orphan Tables
Install the Clean Options WordPress plugin and follow this tutorial.
This plugin will find orphan and unused tables in your database. Using this plugin will help you clean all such tables.
This is a quite critical step and do it only when you are sure of the table names which you are not using.
If you are confused, skip this step.
- Note: Deactivate and uninstall the plugin after using it.
Step 5: Remove Unused Meta Values From Database
(Caution: Technical knowledge needed)
This is something I do once a year and suggest you do it only if you are accustomed with phpMyAdmin.
If you have used phpMyAdmin before, simply follow this easy tutorial and get rid of all unused meta values from your database.
This won’t bring down your database size significantly, but it is useful if you are highly determined to clean up your WordPress database.
Step 6: Use MySql Queries To Reduce Database Size
One of the tables which is a major cause of increased database size is “wp_commentmeta” due to the Akismet plugin.
Before running the below mentioned query, my wp_commentmeta size was 146 MB.
After optimizing, it reduced to 16.1 MB.
Here are two queries which you need to run. Use the WP-db manager plugin > Run SQL query option to execute these queries from the WordPress dashboard.
- Run both SQL commands separately.
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';
Note: For some WordPress installations, there is a chance your database table names might be different from what is used in the above code. It could be something like “wp_commentsmeta”, so don’t forget to check your DB table names and replace them accordingly.
Here are couple more SQL queries which will be useful to reduce database size:
DELETE FROM wp_postmeta WHERE meta_key = "_edit_lock";
DELETE FROM wp_postmeta WHERE meta_key = "_edit_last";
Step 7: Empty and Drop Unused Database Tables
Again, this step is for those WordPress users who have a fair knowledge of WordPress tables.
Under the WP-db manager plugin, you will find an option to “Empty/Drop Table” and from there you can see the list of all the tables in your WordPress DB.
Here you can empty those logs which are not useful and drop those tables which were created by any plugins that you are not using anymore.
For example, I removed “wp_roostsettings” which was created by one of the plugins I deleted in Step 2.
Step 8: Optimize Database and Be Amazed
If you have followed all of the above steps as mentioned, it’s now time to optimize your database and see how much size you have freed up.
As mentioned above, my WordPress database size was 286 MB before cleaning up and after all of the above-mentioned steps, my database size dropped to 96.7 MB.
That’s a huge reduction of 189 MB!
Overall, if you have a busy WordPress site, you should clean your WordPress database once every 2 months, or at least optimize your WordPress database once every 2 weeks.
Go ahead and start working on reducing your WordPress database size and let me know how much space it was before and how much space it is now.
Speaking of fine-tuning your WordPress blog:
- How To Make Money From Your WordPress Blog
- How To Fix All Broken Links Using The Broken Link Checker Plugin
- A DIY Guide To WordPress Search Engine Optimization
Check your domain ranking