Optimize Your WordPress Database

WordPress is an amazing publishing platform. Part of its greatness is how user friendly it is, with Revisions being a particular case in point. I can’t count the number of times that I’ve had to load up a previous version of a post to reclaim some literary nugget that I accidentally deleted in a fit of writer’s angst.

Unfortunately, in order to save me from myself, it has to use quite a lot of database space. Each revision means one more row in the Posts table, on top of the canonical version that you create every time you hit the Update or Draft versions. If you are like me and painfully obsessive about every single word, or you write epic 5,000 word posts, then that one post could have 20 or 30 revisions saved in addition to your working copy.

Multiply that by the number of posts on your blog, and it could quickly run up to the hundreds or thousands of rows.

Now, this isn’t a major problem because MySQL (the database that WordPress uses) is lightning quick and WordPress’ tables are very well structured. However, during the loading of a page WP has to run some pretty complex queries on these tables – cross-referencing posts, post meta, options and authors – and the speed of these queries is dependent on the size of the tables.

Larger tables means a longer wait for the results. On smaller blogs we’re talking milliseconds but as your blog grows it might start turning into a noticeable amount of time. If you have thousands of posts, then it could take a second or more to process.

Add this to the PHP code WP has to process, transmission of files, JavaScript to process and so on and suddenly your blog can seem sluggish and unresponsive.

So, as part of your maintenance and tweaking efforts, it’s worth considering doing a little bit of database optimization now and again to see if you can’t speed things up a little on the server side and, if you’re above 500 posts and have never done it, I’m pretty certain you’ll notice a significant increase.

Backing Up Your Database

Before we do any work on the database, it’s absolutely essential that we take a backup of it just in case. Remember, every time someone skips this step, somewhere a fairy dies.

1) Log into your CPanel account, browse down to PHPMyAdmin and click the icon to open it up

2) Once you’re in, select your blog’s database by clicking on the database name in the right hand panel (note: you might have more databases listed – make sure you choose the right one!)

3) Click on ‘Export’ along the top menu bar

4) Make sure that all of the tables are highlighted here (click Select All if you’re not sure) :

5) Check the box that says Save As File and then give it a name.

6) Click Go, and the browser ‘Save as…’ dialog will pop up. Save this file somewhere safe – I usually put it on the desktop while I’m working with it.

Deleting Your Revisions

Now that you have a copy of your DB saved somewhere safe in case of catastrophe, we can get on to the good stuff.

Step one is to delete all of your revisions from your wp_posts table. Note, I’m assuming that your table prefix is wp_. If it’s not, make sure you replace the wp_ with your table prefix in the following code (if you have no clue what I’m talking about, you can probably safely ignore this).

1) Still in PHPMyAdmin, click on the ‘SQL’ tab at the top.

Enter the following code in the text box:

<br />
DELETE FROM `wp_posts` WHERE `post_type` = 'revision'<br />

WARNING: This will delete ALL of your post revisions and there is NO undo. Make super sure you want to do this (you have taken a backup right? I do worry).

Click Go

It may ask you if you’re sure. If you are sure, click OK.

Optimizing The Tables

Now that you’ve emptied the wp_posts table of all those excess rows, it’s time to let MySQL do its thing to optimize the table.

1) Click the big blue database name in the top left hand corner to go back to the list of your database tables.

On the right hand side of the list, there is a column called Overhead. This tells you roughly how much space in your DB you’ll save once you’ve optimized your tables. On some of my blogs, it was into the tens of megabytes. Not in this one, though. This one’s just for show.

2) Under the list, click on Check All

3) Select Optimize Table from the With Selected: dropdown list.

MySQL will think about this for a second, then give you the results of the optimization. The standard WordPress tables will all be optimized. If you’ve installed some plugins that have created their own tables, these may not have been optimized so don’t worry if you see messages saying that it wasn’t possible.

And that’s it! Wanted more steps? Sorry to disappoint.

All that’s left is to go back and reload your website to see if you notice a bit of a speed increase (results may vary).

For Kamikaze Bloggers

If you find that you never use the Revisions functionality, then you can choose to turn it off entirely to stop WP bulking up your database unnecessarily (I would also recommend setting this option if your site has more than 750 posts and pages).

To do this, simply open up your wp_config.php file and add the following line to the top of the file.

<br />
define('WP_POST_REVISIONS', false);<br />

From now on, WordPress will stop keeping Post Revisions. That means no backups, sunshine. You delete the contents of a post by accident, you’re on your own.

Bonus Easter Egg Fun!

Since we’re talking about Revisions, check this fun little Easter egg out (if you’ve just followed the above instructions, you’ll need to wait until you’ve got some more revisions before this will work – simply leave a post open for a few minutes, then hit Update and you should have some to work with).

1) Select any post, then go down to the Revisions meta box and click on any of the links to open up the Revisions page.

2) Set the Old and New revisions to the same post.

Then click the Compare Revisions button…