While I upgraded my WordPress installation yesterday, I found that my database has a lot of old post revisions. Nothing too bad, but I like it clean! And why should I keep revisions for posts that are older than two to three month?

A while ago, I found the following query:

DELETE FROM wp_posts WHERE post_type = 'revision';

That can do the job, but as far as I understand the WordPress database, the following should be a bit better:

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';

Check your $table_prefix! I never use the default wp_ and change that to something else.

In this example the prefix is “MyPrefix":

DELETE a,b,c
FROM MyPrefix_posts a
LEFT JOIN MyPrefix_term_relationships b
ON (a.ID = b.object_id)
LEFT JOIN MyPrefix_postmeta c
ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';

Everything can be in one long line, but to make it more readable; I decided to separated everything.

Most Tools will combine everything back to one query. I use Sequel Pro and I think that phpMyAdmin will do it as well.

If you don’t like to hack your SQL database: There are some very good plugins available!

However, I don’t to much plugins installed. And I have no problem with firing up SQL queries directly to the database.