Better Optimize WordPress Database – All in One Guide

How to Optimize WordPress Database? Is there any complete guide? Well, try following this tutorial and I bet, you will see better site loading performance after that.

Optimizing WordPress is always been my goal. It’s been almost 10 years I started blogging first in 2008. Since very early days I had just two things in my mind.

1. Produce better content for users
2. Create blazing fast Crunchify.com Site 🙂

As you see in above screenshot, almost all pages on Crunchify.com loads in less than 0.3 seconds. That’s I believe, pretty good compare to other competitors.

As you may have noticed some very interesting articles on how to speed up WordPress site on Crunchify before, in this tutorial we will go over all optimization we need to do on WordPress Database to make site blazing fast.

Before that I would suggest you to go over some articles to speed up site:

1. Leverage .htaccess to speed up WordPress site
2. Stop loading unwanted files on site
3. Better clean up WordPress header section

As you may be aware, WordPress installation created MySQL Database on your hosting provider’s server. Basically it’s nothing but Relational Database with Primary Key and Value store.

WP_Options is a key table which stores all of your settings. WP_Posts is a table which stores all your blog post data and so on. WordPress by design provides very robust and super optimized solution for your site but over time, because of multiple plugin installation, different themes data and unwanted to _transient_ objects, and more junk.

Have any of below questions? Then you are at right place:

• Optimizing Your WordPress Database
• How to Clean Up Your WordPress Database for Improved Performance
• Best plugins to clean up and optimize WordPress database and tables
• Optimizing WordPress database performance

Let’s get started on how to optimize WordPress Database to improve blog performance?

Step-1

Cleanup wp_commentmeta WordPress Table. You absolutely don’t need this. If you are using Disqus or Akismet plugin then both plugins adds lots of junk data which you never used and not gonna use in the future. You could simply delete it without any hesitation.

• Go to cPanel
• Click on phpMyAdmin
• Choose your WordPress database
• Click on Empty link for table wp_commentmeta
• On Confirm dialog just click OK.

Step-2

Clean up all _edit_lock and _edit_last records from wp_postmeta table.

If you have a single author blog then you absolutely don’t need _edit_lock and _edit_last records as it helps you stop accidental overriding other users changes on the same post.

Just use below SQL query to clean up both _edit_lock and _edit_last.

DELETE FROM wp_postmeta WHERE meta_key = "_edit_lock";
DELETE FROM wp_postmeta WHERE meta_key = "_edit_last";

You could also use phpMyAdmin Query editor from cPanel to do the same.

Step-3 Disable Auto Save

By default WordPress save your post every 2 minutes and stores as revisions in Database. It also save latest draft every 30 seconds which has latest copy. If you feel, you don’t need all the revisions of the post then you could simply delete it.

Add below function to your theme’s functions.php file and you are all set.

Detailed post on how to delete post/page revisions.

function CrunchifyDisableAutoSave() {
        wp_deregister_script('autosave');
}
add_action( 'wp_print_scripts', 'CrunchifyDisableAutoSave' );

Step-4 Better use plugin WP Sweep to clean up Metadata

Plugin link. How to Deep-Clean WordPress Database and How To Reduce WordPress Database Size? Below all steps are the solution.

• Stored Post Revisions
• Auto drafts
• Orphaned post meta
• Orphaned comment meta
• Orphaned user meta
• Deleted comments
• Unapproved comments
• Spammed comments
• Deleted comments
• Orphan term relationships
• And much more….

This is the Easiest Way To CleanUP WP Database. Do you have a question on wp sweep vs wp optimize? Well, I would suggest to go with WP Sweep.

Step-5 Please delete unwanted Plugin – I mean it.

This is big one. I absolutely stand behind this. You know, JetPack, Disqus, WooCommerce, WP Post Review Pro and lot more plugins are adding thousands of unwanted records to your DB? Well, if you don’t know that then follow each below tutorials.

• Delete all unwanted records for plugin Disqus
• Remove all Geo IP location data from Database added by WooCommerce
• Remove Akismet data from DB
• Remove Transient Variable on Post Publish
• Remove all _jetpack_transient Object from WP_Options table

Oh really?? Do I have to perform all above steps? Well, yes if you used any of above plugins before and not using right now then I would suggest strongly to go over each Database optimization.

Step-6 Empty and drop all unused Database Tables

Here is a simple example. Let’s say you used Easy Digital Download on your site before. In order for it to run, plugin creates 3 Database tables. Now as you may moved over your premium subscription site to another or altogether stopped using EDD then there is no point of keeping those 3 tables in your live site’s Database.

Step-7 After all above steps, Optimize your Database

On Crunchify, we are using plugin WP-DBManager for DB backup in addition to VaultPress and optimizing Database.

I hope these steps help you boost up your site speed. Eventually fast site will rank high in Google SERP and will attract more visitors.

Happy blogging.