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
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 sowp_{id}_posts
becomeswp_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:
I suggest proceeding like this:
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:
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.
Run SQL query:
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):