Want to optimize your WordPress database?
Continue reading…
A typical WordPress installation has two important components:
- WordPress files stored on your server
- WordPress Database
It is essential to regularly clean up your WordPress database and reduce its size. Especially if you are running a Woo-Commerce store on WordPress, this is something you should do regularly.
Over time, your WordPress database accumulates many redundant tables, unused records, huge wp_postmeta, and many entries that you can delete without affecting your website.
This is a must-do process for every WordPress user to keep their database size to a minimum and keep their WordPress blog loading fast.
This will help reduce your server load and your WordPress performance will improve significantly.
For this tutorial, I will use SidelinePlay's WordPress database, which is 286.3 MB , as an example and reduce its size significantly.
You can follow this guide step by step to perform these tasks on your own blog.
Note: Some of the points require some technical knowledge, if you have questions, feel free to ask in the comments or skip the step entirely.
Also Read: How to Improve WordPress Performance with Advanced Database Cleanup Plugins
Tutorial: How to Optimize Your WordPress Database Size
Step 1. Back up your WordPress database (Important)
This is an essential step .
In my case, I used my hosting company’s backup feature to make a complete backup of my database and all the files it contained. If your hosting company offers a backup feature, use it before following the steps below to optimize your WordPress database.
You can also use the WP-DB Manager plugin to back up your database files. We will also use the WP-DB Manager plugin to run some SQL queries, clear and delete database tables (all of which the plugin can do).
Step 2: Disable and remove unused WordPress plugins
Go through the list of active plugins on your WordPress blog and disable the ones you no longer use. There may be some plugins you use occasionally, but I recommend disabling them now and reinstalling them when you need them again.
Our goal today is to completely optimize the database size.
Simply disabling unused plugins won’t help much here, so make sure to remove those as well.
Speaking of useful plugins:
Step 3. Delete all spam comments, spam posts, and post revisions
Delete all comments in spam and junk folders.
Likewise, delete all posts from the trash folder and remove all post revisions.
- Install and use the Advance WordPress Database Cleanup plugin to do this in one click.
Step 4. Find and clean up 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 up all such tables.
This is a very critical step and should be performed only when you are sure of the table name being unused .
If you are confused, skip this step.
- Note: Please deactivate this plugin after use.
Step 5. Delete unused meta values from the database
(Note: Technical knowledge required)
This is something I do once a year and I recommend you do it only if you are familiar with phpMyAdmin .
This will not significantly reduce your database size, but it can be useful if you are determined to clean up your WordPress database.
Step 6. Useful MySQL queries to optimize WordPress database
One of the main reasons for the increase in database size due to Akismet plugin is the table “wp_commentmeta”.
Before running the below mentioned query, my wp_commentmeta size was 146 MB.
After optimization, it was reduced to 16.1MB.
Below are the two queries you need to run. Execute these queries from your WordPress dashboard using the WP-DB Manager plugin > Run SQL Query option.
- Run the two 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, your database table name may be different than the one used in the above code. It may be something like “wp_commentsmeta” so don’t forget to check your database table name and replace accordingly.
Here are some more SQL queries that can help reduce your database size:
DELETE FROM wp_postmeta WHERE meta_key = "_edit_lock";
DELETE FROM wp_postmeta WHERE meta_key = "_edit_last";
Step 7. Clear and delete unused database tables
Again, this step is for WordPress users who have some knowledge of WordPress tables.
Under the WP-DB Manager plugin, you will find the “Empty/Delete Tables” option, from there you can see a list of all the tables in the WordPress DB.
Here you can **clear out unused logs **and delete tables created by plugins that are no longer used.
For example, I deleted "wp_roostsettings" which was created by one of the plugins I deleted in step 2.
Step 8. Optimize the database and bring surprises
If you have followed all the steps above, now it is time to optimize your database and see how much space you freed up.
You can optimize your database using the Optimize option of the WP Advanced Database cleaner plugin.
As mentioned above, before the cleanup my WordPress database size was 286 MB , after completing all the above steps, my database size dropped to 96.7 MB.
That’s a massive reduction of 189 MB!
In general, if you have a busy WordPress website, you should clean your WordPress database every two months, or at least optimize your WordPress database every two weeks.
Go ahead and start working on reducing your WordPress database size and then let me know how much space it had before and how much space it has now.
When it comes to fine-tuning your WordPress blog:
- How to Monetize Your WordPress Blog
- How to fix all broken links using Broken Link Checker plugin
- WordPress SEO Tutorial (From Beginner to Advanced Guide)
Disclosure: Some of the links in this article contain affiliate links, which means we may earn a commission if you click through to visit us, at no extra cost to you. See how SidelinePlay is funded, why it’s important, and how you can support us.
Was this helpful?