Cleaning Up wp_posts and wp_postmeta

One annoying feature (at least to me) is that WordPress will automatically store an unlimited number of revisions for every post on your site. This can, to say the least, create huge wp_posts and wp_postmeta tables.

You can also decide that you just want to delete some stuff. Unfortunately this all just leads to lots of extra junk stuck in your database.

The good news it’s really simple to clean everything up to get your site running it’s fastest again. Just run a few SQL commands and install a plugin and you’re all set.

Installing the plugin

First things first. Make sure to install Revision Control. It’s one plugin that goes on every site I build. I normally set it to keep only 1 or 2 revisions. I don’t remember the last time I used a revision, but keeping one or two won’t hurt.

Optimizing the database

Now comes the cleaning of the database. For quick tasks like this I like to use the command line, but these queries can also be run from phpMyAdmin or a similar tool. Just make sure you’ve selected your WordPress database.

It should be noted that before doing anything to any database ever you should make sure you take a backup that you can restore.

I’m going to assume that your prefix is wp_ and that you are doing this on a single site install. If your prefix is different or you’re using multisite you’ll need to change the database table names.

The first thing we’re going to do is delete any revisions that don’t have a parent post. There shouldn’t be any, but this is a just in case step.

Now you shouldn’t have any more orphaned revisions.

Next up we need to delete the unneeded junk from wp_postmeta.

And now your junk is gone from wp_postmeta.

The problem is after deleting stuff from a database you’re left with what’s called overhead. It slows your site down and needs to be cleaned up. Two more queries is all we need.

If you’re using phpMyAdmin you can also scroll to the end of the list of your tables and click Check All Tables Having Overhead and the select Optimize Table from the dropdown menu.

Now you’ve gotten your database in tip top shape.

Leave a Reply