This is the second part of managing WordPress feature of post revisions, for the users who had disabled or turned off post revisions tracking or versions history feature. As you already know, this feature exists in WordPress (starting with WordPress 2.6). After disable it (of course, if you really need to do that), it also make sense to also delete and remove all existing stored post revisions. By doing so, you’ll remove changes made on pages stored in the database such that you’ll slim down the wp_posts table sizeÂ´. This is especially recommended when there is already lots of revisions or changes inside.
For this process , you will get your hands dirty in some SQL statements. For doing that, you’ll need to delete and remove all existing post revisions (not all posts!)entries from WordPress database Posts table. We’ll start simply, by logging into MySQL command-line interface, phpMyAdmin, SQLyog or other MySQL GUI tool. Next, select the respective WordPress database (just in case that you have on the same server more than one database).
First time when I looked into this matter, a simple sql statement like the one below will do the trick.
DELETE FROM wp_posts WHERE post_type = "revision";
But hey, there are other information associated with this table, how I later found out, stored in other tables, eating space also in other areas of database.
Then I found out that all you have to do is to issue the next command (found on Andrei Neculau’s website and modified a little by me for better visibility). But before is highly recommended to backup the database before performing the deletion SQL statements:
DELETE P,TR,PM FROM wp_posts P LEFT JOIN wp_term_relationships TR ON (P.ID = TR.object_id) LEFT JOIN wp_postmeta PM ON (P.ID = PM.post_id) WHERE P.post_type = 'revision'
After all post revisions are deleted, the users will no longer be able to verify the changes by phase or diff between versions. Thus, this process can be used also by an admin in order to ensure privacy or not letting authors or writers in the blog knowing their posts have been edited.