It is very much essential to properly optimize a WordPress database so that it doesn’t slows down your website. A single WordPress installation uses a single database for everything, as it stores posts, pages, comments, users, menus and everything that you can ever think of. Only the images, video and other media that you upload via the Media Uploader are stored in the wp-content folder, but apart from that, everything is stored in the database.
Why should you optimiza your WordPress database
Now, as time passes, the size of WordPress database increases, and gets accumulated by even some non-useful stuff such as post revisions, spam comments, un-approved comments, pingbacks and trackbacks. All these will make your database bloated and will ultimately slow down your site. That’s why the need to optimize WordPress database.
A small and perfectly optimized database runs much faster when compared to a large database. Smaller database means faster response to database queries, and thus a faster running WordPress site not just for the visitors but also for the admin who’s writing posts.
When talking about the WordPress database, spam comments and post revisions affect the most in database loading time. Mostly every popular site out there is facing the problem of spam comments, and that’s why the need to remove them from the database.
Post revisions are something that happen automatically, as WordPress keeps a post revision every time you click on the Save Draft button, and sometimes automatically also. For example, if you have a post which is approximately 100KB data and you have 5 revisions of that post, the total space wasted is about 500KB, when actually there should be only of 100KB. And if you have 100 posts similar to it, you have 50MB database space wasted.
How to speed up your WordPress database
Now that you know how important it is to run an optimized WordPress database, let’s learn how to optimize and clean up the WordPress database. A plugin named as WP-Optimize will help you in this task, which means you won’t need to manually head over to the phpMyAdmin and do any tinkering.
Install and activate the WP-Optimize plugin. Once activated, you’ll be able to see WP-Optimize option on the sidebar of the admin interface. Click on it and then you’ll be able to see its main screen where the action happens.
Next, check mark on all the options, or leave the default options as it is. From all these options, you would definitely like to checkmark these three options: Remove all post revisions, Remove auto draft posts and Remove spam comments. Also checkmark on “Optimize database tables” and then click on the “Process” button.
Now the plugin would start the process and it could take a minute or two depending on the database size and the server. Once the optimization process is complete, you’ll be able to learn how much is your current database size and how much you’ve saved by using this plugin.
If you prefer to manually optimize some specific tables, then scroll down in the same page and click on the “Need to optimize” link. However, I’ll recommend you to do it the above mentioned way.
From the Settings page of this plugin, you can also enable scheduled clean-up and optimization. You can schedule this to happen every week, every other week or monthly. However, this feature is in experimental stage right now and that’s why using this method is not recommended.
So that’s it. Go ahead and speed up your WordPress database with this method.