Safest way to bulk delete post revisions

Question

One of my clients is on a rather large blog in terms of number of posts and traffic. I'm trying to get her database down to a manageable size, and one thing that's bulking up is literally tens of thousands of post revisions.

I've already set Wordpress config to limit the number of revisions in the future to two:

define('WP_POST_REVISIONS', 2);

But I want to delete all existing revisions.

Question 1: Is it safe to directly delete all rows in the wp_posts table that have a post_type of revision? (I've seen conflicting answers on this—but I'd love to be able to just do it this way if it's safe).

Question 2: …and this is only relevent if I should NOT just do the straightforward delete from question one:

I found this answer where songdogtech provides a database query to safely delete, but (1) it's specifically in answer to a multisite question (this is a single site) and (2) I have just upgraded the site to 3.6, which included database changes. (So, I'm not skilled enough in reading database queries to know exactly what's going on there and if it would work for a single site in WP 3.6

Answer 1

Is it safe to directly delete all rows in the wp_posts table that have a post_type of revision? (I've seen conflicting answers on this—but I'd love to be able to just do it this way if it's safe)

Safe, it's safe.

If there is only one user (you) that can edit posts on the site it's safe and does not cause any other problems.

If there are more users, and one is editing a post and in the meantime you delete revisions it still isn't unsafe, but can be annoying for that user seeing revisions disappear.

What is absolutely unsafe is to run the SQL query on the WP database without taking one (or better, more) affordable backup(s) and testing the query on the local/dev environment beforehand.

Let's imagine you accidentally type 'post' instead of 'revision', if you have no backups and you run the query on the production site, what happens?

Regarding the second question, just delete {id}_ everywhere it appears in the query posted so wp_{id}_posts becomes wp_posts and so on.

A warning, the wp_ part is the standard table prefix, that cool guys change to something different during WP installation.

If you have changed it and in your wp_config.php you see $table_prefix = 'something_else_than_wp_';

Your query becomes:

DELETE a,b,c
FROM something_else_than_wp_posts a
LEFT JOIN something_else_than_wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN something_else_than_wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

I suggest proceeding like this:

1. Backup the database
2. Backup the database again
3. Test the backup by restoring the database in another database
4. Change your 'wp_config' to use this new database
5. Run the query on the new database and check if there is something that goes wrong
6. If not, you are finished. If so, change 'wp_config' again an let it use the old database and try to investigate the problem.

Answer 2

The details provided so far are incomplete at best, and the a,b,c query is not good - potentially even dangerous. It forgets to factor in a lot of the potential dependencies. There is a full discussion and better queries here

There's also this revised version of the query which should be much better, but test in a low risk dev environment and backup:

Specifically:

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 )
LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
WHERE a.post_type = 'revision'
AND d.taxonomy != 'link_category';

This query handles older data where WordPress might be using the same object_id in the wp_term_relationships table for both a post and a link. By running the other versions of this a,b,c query, you can unintentionally delete link data as well. This is not as much of an issue with newer installs of WordPress.

If you run that version of the query and get 0 deletes, it just means you have no 'link_category' entries in your wp_term_taxonomy table. You can verify by checking that table, and then just remove that last line and run the query again.

But be sure you backup, test, and verify the results before using on production data. This query took one of my revision-bloated wp_posts table from 300 MB down to 5 MB after optimization.

Answer 3

Run SQL query:

DELETE FROM wp_posts WHERE post_type = "revision" // for "wptest" DB, note the table name

NOTE: The above query “just deletes post marked as revisions. If for some reason you associated a revision with a tag or a category that was then removed when the final post was published, you will have extra entries in other tables such as terms.” The proper query to safely remove all of your revisions is as follows (change the table prefix as necessary):

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'