This is actually SolidlyStated.com’s first post ever regarding WordPress itself, which is the engine that powers this website.
This article is nothing new and is really going to benefit me the most, because deleting post revisions is something that is done every couple of weeks here. Now I will have a permanent record of this SQL statement.
When searching Google for this query, you will see a simple version that you should not use. This one-liner below will delete revisions, but leave behind any meta data in your database. Always backup your blog database first.
Deleting Old Post Revisions
Avoid this commonly seen query.
Here is a better query to delete all post revisions.
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' |
Disabling Post Revisions
If you also want to disable the post revision system, you can add this commonly blogged about line near the bottom of your wp_config.php file.
define('WP_POST_REVISIONS', false); |
Though it won’t technically prevent you from adding meta to a revision, the various functions for adding and updating post meta check the post ID with wp_is_post_revision($post_id) and will “correct” a revision ID to the published post ID before adding the meta.
The taxonomy functions don’t have the same check for wp_is_post_revision(), but the admin tools only operate on the published version of the post.
The suggested query is technically correct, but a database that’s so bloated as to need pruning of revisions is at risk of falling over when trying to join those three tables. Given the unlikelihood that postmeta or taxonomy terms are associated with a revision, the simpler query might be a better option.
In theory, maybe, but it would be just as unlikely to have a database of posts large enough to lock up on the JOIN query. It’s not parsing post content, so even a million records would be fast.