Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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 site it's safe and does not bring any other problem.

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

What is absolutely unsafe is to run sql query on WP database without taking one (or better more) affordable backup and before have tested the query on local/dev environment.

Let's imagine you are absentminded for a while and you type 'post' instead of 'revision', if you have no backups and you run query on production site, what happen?

Regarding second question, just delete {id}_ everywhere it appears in the query posted by @songdogtech so wp_{id}_posts became for you wp_posts and so on.

But warning, the wp_ part, it's the standard tables prefix, that cool guys change with 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 became:

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 to proceed in like this:

  1. Backup DB
  2. Backup DB Again
  3. Test the backup restoring DB 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 done. If yes, change 'wp_config' again an let it use the old db and try to investigate the problem
share|improve this answer
    
Thank you. Exactly what I wanted to hear. And yes, to all of the backup, etc. –  StudioAl Sep 4 '13 at 0:34

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'

share|improve this answer
    
Thank you for the clarification regarding the queries. Makes perfect sense. –  StudioAl Sep 4 '13 at 0:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.