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?
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
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.
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?
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:
Thus with WP-Optimize, we’ve saved 16.98 MB – that’s 53.31% of the total database size saved!
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:
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!