How to Optimize the WordPress Database

Every WordPress installation uses a single database for storing posts, pages, menus, links users, comments – essentially all forms of textual and encrypted data. However, the stuff that you upload via the Media Uploader such as images, MP3s, videos and other files aren’t stored in the database. They are stored in the ‘wp_content’ folder. As time goes by, the WordPress database grows in size and generates some garbage data which inflated your WordPress database making it slower.

Why Optimize the WordPress Database?

Space Savings

Maintaining and optimizing the WordPress database is an important task and should not be undermined.

Hang on, take a backup now

Before we proceed any further, please take a backup of your WordPress database. If possible, ensure that the backup database is working. Once you create the backup, simply edit the config.php file (present in the WordPress root directory) to use your backup database and ensure everything runs properly.

A small and optimized database performs much faster compared to a large, cluttered one. Smaller database implies faster response to database queries made by WordPress, which in turn makes your WordPress site perform faster – not just for the visitor, but also for the people writing and updating posts.

What is “Garbage Data”?

The term ‘garbage data’ is used to signify information that needs not be present in the database. Examples of such garbage data would include:

  • Comments in the spam queue
  • Unapproved comments
  • Post revisions
  • Trashed items such as posts and pages

Spam Comments

Out of these, spam comments and post revisions affect the database the most. If your site has a moderate number of visitors and has comments enabled, then rest assured, you’ll be seeing a lot of spam comments. Akismet will automatically filter the spam and put it in the spam queue. However, the comments are still present in the database! And you really have no use for them.

Optimize Database

Pre-optimization statistics

As for post revisions – its a slightly different matter. While writing a post, every time you hit the “Save Draft” button, a post revision is created and saved. Specially for long articles, you write it part by part and save every now and then. Imagine the total size of your article is 50KB and you save it say 10 times. That’s 500KB space consumed in the database, which in actuality should consume 50KB. That’s 450KB of space wasted per long article. Now imagine you have 100 such long articles in your site. That’s 45,000 KB or 45 MB of space wasted in your WordPress database!

How to Optimize the WordPress Database?

Menu Location

WP-Optimize is an excellent plugin that helps you clean and optimize your WordPress database. I’ve tested the plugin with WordPress 3.6.1 and found it to be working perfectly! Install and activate the plugin and navigate to its main present in the left sidebar in the admin dashboard.

Next, check all the options (refer “Pre-optimization statistics” image) and press the “Process” button. This could take a minute depending on the size of your database and the server. It should probably take more time in a shared hosting server. Once the optimization is complete, you should get detailed report of the values that have been removed from the database. Here’s an example:

Total Savings

Thus with WP-Optimize, we’ve saved 16.98 MB – that’s 53.31% of the total database size saved!

Download WP-Optimize

Other cool tricks

You could also disable post revisions feature completely. However, I strongly recommend against it, since it creates more problems than solutions. If you want to disable it, enter this code in your wp-config.php file:

define('WP_POST_REVISIONS', FALSE);

Another efficient way of dealing with bulky databases is to automatically empty the trash, at regular intervals. The following line of code will automatically purge all trashed items every 10 days. Add it to your wp-config.php file:

define('EMPTY_TRASH_DAYS', 10 );

You could change 10 to any number of days  you want. However, that value must be greater than 0.

Thus we’ve learnt how to optimize a WordPress database with nothing but a few clicks of the mouse. What plugin do you use to optimize your WordPress database? Better yet, post some of the statistics here once you run this plugin for the first time!

Sourav
Post Author: Sourav

Sourav is a WordPress enthusiast, an avid gamer and a sitcom collector. His playlists include heavy metal, electronic, and new-age tracks. When he's not online, he's spending quality time with his friends and family. You can follow him on Twitter.

Disclosure: This page contains external affiliate links that may result in us receiving a comission if you choose to purchase said product. The opinions on this page are our own. We do not receive payment for positive reviews.
Got something to say? Join the discussion.
  1. nice blog Sourov........ keep it up..
  2. makeupsubscribe says:
    Thank you for good tips. I really don't like the revision and the code to empty trash is so helpful. Thanks a lot.
  3. marywang says:
    This is just what I am looking for. I am debating whether I should have revision history saved. Because it can be useful sometimes.
    • Darren Turpin says:
      You can also limit the number of post revisions via wpconfig.php by adding: define('WP_POST_REVISIONS', X); Where X is the number of revisions you want to keep (I usually go for 6).
      • Sourav K says:
        Sourav K
        Thanks Darren, great tip! :D
        Author
      • Looks good. Must not work on current articles, though. I looked at one of my posts with a long list of revisions. Nothing changed after I added the above code to the bottom of my wp.config file (used the number "3").
  4. rate comet says:
    Great tip, never knew you could do that. So many plugins out there, makes it hard to find which ones are useful and which ones are not.
  5. Sneha Malik says:
    Hi Sourav K ... Its a great WP plugin ... Hosting companies provide limited space for databases and filling your database with unwanted copies of article can force you to buy additional database space but this plugin really helps non-techie guys to cleanup database.
  6. sivasakthivel says:
    Great Blog Sourav.. Thanks.
  7. thanks for sharing nice trick
  8. Telecharger Fraps says:
    Valuable info. Fortunate me I found your website unintentionally, and I am stunned why this accident didn't happened in advance! I bookmarked it.
  9. How do you fix the load time? is it something to do with the size of the images and thats why the page is slow? need help
    • AJ Clarke says:
      AJ Clarke
      Depends on the site there are many issues that can affect page speed, try contacting your webhost they should help you out.
      Admin
  10. Bilqees Kenchi says:
    Nice collection. Woothemes has a new ecommerce theme called “Superstore” that is pretty good also.
  11. sewa mobil di solo says:
    Very good written information. It will be beneficial to everyone who employs it, including myself. Keep up the good work - looking forward to more posts.
  12. sewa mobil di bali says:
    I am actually glad to read this webpage posts which contains lots of valuable facts, thanks for providing such statistics.
  13. Very nice write-up. I certainly love this site. Continue the good work!

Leave a Reply