Want to optimize WordPress database?
Read on…
There are two important components of a typical WordPress installation:
- WordPress files stored on your server
- WordPress database
It’s an essential for you to clean up your WordPress database and reduce its size from time to time. Especially if you are running a Woo-Commerce store on WordPress, this is a regular stuff you should be doing.
Over time, your WordPress database has accumulated many redundant tables, unused records, huge wp_postmeta 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 reduces its 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 Optimize WordPress Database Size
Step 1. Take WordPress Database Backup (IMPORTANT)
This is an essential step.
In my case, I have used my hosting company’s backup feature to take a complete backup of the database and all included files. If your hosting, offers backup feature, then use it before you follow the steps below to optimize WordPress database.
You can also use the WP-DB Manager plugin to take a backup of your database file. We will also be using WP-DB Manager plugin 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.
Step 3. Delete All Spam Comments, Trash Posts, and Post Revisions
Whatever comments you have in your spam and trash folders, delete them.
Similarly, delete all posts from the trash folder and delete all post revisions.
- Install and use the Advance WordPress database cleaner plugin to get this done with one click.
Step 4. Find and Clean Orphan Tables
Install the WP Advanced database cleaner WordPress plugin.
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 does it only when you are sure of the table names which you are not using.
If you are confused, skip this step.
- Note: Deactivate 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 suggests you do it only if you are accustomed to phpMyAdmin.
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. Useful MySql Queries To Optimize WordPress Database
One of the tables which are 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.
You can optimize your database using the “Optimize” option of the WP Advanced Database cleaner plugin.
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 two months, or at least optimize your WordPress database once every two weeks.
