Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

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,

share|improve this question
    
I'm not sure but every now and again I do a "DELETE FROM wp_posts WHERE post_type = 'revision'"; And drop all the revisions. I don't think the front end does anything with revisions. Its only applicable to the backend/post editor. So for the end user its immaterial really. I wouldn't put a limit on it. I'd just tidy up every now and again, or refine my query to remove revisions of a certain age from the table. The only thing it does is clean up the Database index(es). –  Barry Carlyon Jul 15 '12 at 17:53
    
Additional: it will probably only mak a significant difference on very large websites with 10k+ posts I think. –  Barry Carlyon Jul 15 '12 at 18:00
add comment

1 Answer

up vote 4 down vote accepted

Michael Adams, Quantum Bug Creator at Automattic once said:

"Revisions are stored in the posts table. I don't yet know what kind of impact that will have on post queries. We're going to turn the switch to "on" on WordPress.com sometime soonish and we'll have a better idea about what kind of damage, if any, it does."

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.