Will removing revisions positively impact database performance?

1 Views

As per this question over on DBA.SE, I’m having quite a few issues with database optimisation.

I’m just wondering if removing/disabling revisions will positively impact my database usage? We don’t seem to use them as often as I was expecting.

Read More

Currently I’ve got it set so that each post can have up to 3 revisions.

If removing these will improve performance, what’s the “up to date” way of removing revisions from the database?

Thanks in advance,

Post Views: 1

Related posts

Leave a Reply

1 comment

  1. And sometime later they did turn it on, and WordPress.com now stores the last 25 revisions for each post on a blog. So, yeah, you can deal with revisions.

    The problem with post revisions is that your database size can grow really big (depending on the number of revisions created) and it in turn adds to MySQL’s processing burden.

    Nevertheless, you can deal with it. Enable page and database-level caching — W3 Total Cache does this, and if your website is served from a single server, try WP Super Cache. And then there are various web application accelerators like Varnish cache. Websites with tens of thousands of posts have to do sharding, slave and master replication of database and stuff anyway.

    Even then, revisions on (very?) old posts is a waste of resources, IMO. As for me, I delete revisions of published posts from time to time (every 2-3 days).

    Deleting Revisions:

    Backup, backup, backup first. Always!

    If you want to use a plugin to delete revisions and optimize database, and keep things simple, try Better Delete Revisions. You might also want to take a look at Revision Control.

    Or you can do it manually using SQL as shown below.

    (1A) Delete revisions of all posts and pages on your WordPress blog (source):

    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';

    (1B) If you use the Links feature in WordPress (Blogroll, etc), DON’T use the aforementioned SQL query. Use this instead (read why?):

    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 )
LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
WHERE a.post_type = 'revision'
AND d.taxonomy != 'link_category';

    (2) Delete auto-drafts:

    DELETE FROM wp_posts WHERE post_status = 'auto-draft';

    (3) Optimize your database tables:

    OPTIMIZE TABLE `wp_commentmeta`, `wp_comments`, `wp_links`, `wp_options`, `wp_postmeta`, `wp_posts`, `wp_terms`, `wp_term_relationships`, `wp_term_taxonomy`, `wp_usermeta`, `wp_users`

    NOTE: In the SQL queries listed above, don’t forget to replace wp_ with your database table prefix.