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 timely cleanup WordPress database and reduce the size. This is a must-have process for every WordPress user to keep the size of their database at minimum and keep their WordPress blog fast. Over the time your WordPress database have many redundant table, unused records and many entries which you can remove without affecting your website. This will help you a lot to reduce load on your server and your WordPress performance will improve.
In this tutorial I will take example of ShoutMeLoud WordPress database which is of size 286.3 MB before cleaning up, and will share exact steps I took to drastically reduce the size of my blog database. You can follow this guide step by step and work on your blog. Only few of the points might require technical know-how, and if you find yourself questioning there; feel free to ask question or skip that step. So, let’s get on with this simple DIY tutorial.
- Useful read: A DIY guide to WordPress search engine optimization
Tutorial: How to Reduce WordPress Database Size
I’m mentioning steps in the series I followed to reduce the database size, and I suggest you to follow the exact methods.
Step 1: Take WordPress database backup
This is most essential step which you need to take before you work on your blog database. In my case I have used my hosting company backup feature (WPEngine backup feature) to take complete backup of the database and all files. You can use WP-db manager plugin to take backup of your database file. You can find tutorial over here to learn taking WordPress backup. My recommendation here is WP-db manager plugin as we will be needing to run few SQL query, empty and drop database tables with this one useful plugin.
Step: 2 Disabled and delete unused WordPress plugin:
Now go through the active plugin list of your WordPress blog and first disable those plugins which you are not using anymore. There might be plugins that you use once in a while, So I suggest you can disable them too and install when you need it again. Our goal for today is to completely optimise the database size. Only disabling will not be of much help here, so make you also delete those unused plugins. The reason being is to clean out the database table that are created using those unused plugins.
Need some useful plugins?
- Basic WordPress plugins for every blog
- Must have SEO WordPress plugins
- List of plugins that I use here at ShoutMeLoud
Step : 3Delete all spam comments, trash posts and post revisions:
What ever comments you have in spam folder and trash folder, empty them. Similarly delete all posts from trash folder and delete post revisions. You can use WP-optimize plugin to get this done with one click.
Step : 4 Find and clean orphan table:
Now follow this tutorial and install and use Clean options WordPress plugin to find orphan and unused tables in your database. Using this plugin you can clean all such tables. This is quite critical step and use it when you are sure of the table names which you are not using. If you are in confusion, I would recommend skip this step.
Note: Deactivate and uninstall the plugin after using it.
Step: 5Remove unused Meta value from Database (Caution: For tech people only)
This is one step which I use once in a year and suggest you to do it only if you are accustomed with PhpmyAdmin. If you have earlier worked on PHPmyadmin, you can simply follow this easy tutorial and get rid of all unused meta value from database. This won’t be decreasing down your database size significantly but again this is useful if you are highly determined to clean up your WordPress database.
Step : 6 MySql Queries to reduce database size
One of the table which is a major cause of increased database size is wp_commentmeta due to Akismet plugin. Before running below mentioned query, my commentmeta size was 146 MB and after optimisation it reduced to 16.1 MB . Here are two queries which you need to run and you can use WP db manager plugin > Run SQL query option to execute following queries from WordPress dashboard. Run both the SQL command 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 many WordPress installation there might be chances your database table name might be different from what is used in above code. Ex: wp_commentsmeta, so don’t forget to check your DB table name and replace it 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:
This step is again for those WordPress users who have a fair knowledge of WordPress tables. Under 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. Here you can empty those logs which are not useful and drop those tables which was created by any plugins that you are not using anymore. For example I removed wp_roostsettings which was created by one of the plugin I deleted earlier today.
Step : 8 Optimize database and you will be amazed:
If you have followed all the above steps as mentioned, not it’s time to optimise your database and see how much size you have reduced from your database. You can optimise your database using Optimize option of WP-db manager plugin or you can use WP optimise plugin. I have shared a guide before on Database optimisation here.
As mentioned above my WordPress database size was 286 MB before cleaning up and after all the above mentioned steps my database size is 96.7 MB. Which is a huge reduction of 189 MB. Here are articles which you should read to fine-tune your WordPress blog:
- How to make money from your WordPress Blog
- Learn how to fix all broken links of your blog using broken link checker plugin
Overall if you have a busy WordPress site you should clean your WordPress database once in every 2 months or at least optimise your WordPress database once every 2 week. Go ahead and start working on your WordPress database size and let me know how much space it was taking before and how much space it’s taking after following the above tutorial.