Trimming the Fat

I did it!

<pats self on back>

OK, with that said, I finally found this great SQL query for deleting old WordPress draft revisions.

What this means to Tripawds Bloggers

You may have noticed the Save Draft button in your Tripawds Blog post editor. This will do just that – save a current working draft of your post. Actually doing so, however, is unnecessary considering WordPress has it’s own auto-save function built right in. But if you are writing an important post and concerned about losing your work, go ahead and save your drafts. I am no longer concerned about database bloat!

Scroll down beneath your post editor after saving a draft and you will notice a section called Post Revisions. Clicking the link there will allow you to review the latest draft you saved, compare it to the most recent auto-save, and restore whichever one you prefer.

Why only one revision, and How?

Anyone familiar with WordPress may wonder why only one revision shows, no matter how many times the post is saved. Those very familiar might be asking how we did that.

Every saved post revision is stored in the WordPress database. Over time, especially in a WordPress MU environment, and particularly with users who tend to frequently save drafts, this can create excessive unnecessary database entries – said bloat I eluded to earlier. By limiting revisions to one draft, we are still allowing members to save important drafts and compare that draft with the current auto-save, while keeping unnecessary entries out of the database. FYI: Every draft manually saved will replace the most recent revision.

How did we do it? We are running the Limit Post Revisions WordPress MU plugin from the wpmudev.org project repository. I know, I know, limiting post revisions will not delete old drafts from the database, and that’s what truly enquiring minds want to know. We’re getting to that.

What use this is for WPMU Site Administrators

Limiting post revisions can also be done with this simple hack to your wp-config file, immediately following the db_collate definition, as in…

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');

/** Number of saved revisions; false acts as 0 */
define('WP_POST_REVISIONS', 1);

But I like to avoid editing core files whenever possible, and much prefer to use awesome WPMU plugins. But I digress, back to the task at hand – how to delete all those old revisions in the MySQL database driving your WordPress installation…

Here is the query I used, edited for use with WordPress MU:

DELETE a,b,c
FROM wp_{id}_posts a
LEFT JOIN wp_{id}_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_{id}_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

Simply replace {id} with the id for the blog on which you want to delete old post revisions. Backup your database first! To be safe, I also exported copies of the affected tables so I could restore them quickly if anything broke. But nothing did, at least not that I can tell, yet. 😉

In our case, I reduced file size of our wp_1_posts export from more than 11 MB to less than 2MB! Please note, this worked for me. No promises, and good luck!

Many thanks to Andrei for providing this query, and explaining how it will remove related entries from the postmeta and term_relationship (i.e.: tags, categories, etc.) tables. I found many references to the following query for deleting post revisions, but Andrei’s solution gave me the confidence that all unnecessary data would be deleted from the database.

DELETE FROM wp_posts WHERE post_type = "revision";

While we’re at it, for anyone looking to delete all old post revisions from a basic WordPress install, here ya go…

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

Hope this helps someone, as much as it helped me!