If you look into the WordPress database, table wp_posts, you’ll see lots of records … much more then the actual displayed posts and attachments combined. The reason lies in revisions.
Among others, by default, every post save creates a revision. For the blog, I’m managing, the statistics are: 809 posts and 6287 revisions. And I must mention that I already deleted the revisions for first 391 posts. That means above 15 revisions per one post.
This does not present a big problem if you do not use a plugin such as Yet Another Related Posts Plugin. These related-posts plugins create a fulltext index on post title and content. And that means, if you want speedy searching for the related posts, big indexes – in my case 55 MB. And of that size, 75% is never used. And if didn’t have deleted some revisions before, the MBs and percentage would be even higher. And on webhosting servers this can present a problem because of high memory usage.
Unfortunately MySQL does not (yet) support partial indexing with which you could disable indexing of the revisions. Because of this revisions must be deleted. Some plugins are addressing this problem, but I was unable to find one, which deleted records older than certain number of days. It’s only all or nothing.
Searching the web (I’ll include the link if I stumble upon it again) I found a deletion code and added a time filter (use at your own risk):
DELETE p, tr, pm -- SELECT p.*, tr.object_id, pm.meta_id FROM wp_posts p JOIN wp_posts pp ON pp.ID = p.post_parent LEFT JOIN wp_term_relationships tr ON tr.object_id = p.ID LEFT JOIN wp_postmeta pm ON pm.post_id = p.ID WHERE p.post_type = 'revision' AND pp.post_type = 'post' AND pp.post_status = 'publish' AND p.post_date < ADDDATE(CURDATE(), INTERVAL -2 MONTH)
This can then be used to make a cron job (Linux) or schedule (Windows) to periodically delete old revisions. Linux example: 'mysql --user=? --pass=? --execute "INSERT CODE HERE" DATABASE'.
0 Comments.