I was working on a content-heavy website that have a very big wp_posts
table. This obviously caused by a revisions. To confirm this I needed to find out which where the posts with the most number of revisions. As I had guessed, I found out revisions where killing the wp_posts
table. Quite a few posts had as many as 100+ revisions.
To find this out I came up with this SQL query then I ran in phpMyAdmin:
SELECT parent.ID, parent.post_title, count(children.post_parent) AS children_count
FROM wp_posts as parent
LEFT OUTER
JOIN wp_posts as children
ON children.post_parent = parent.ID && children.post_type = 'revision'
WHERE parent.post_parent = 0
GROUP
BY parent.ID
ORDER BY children_count DESC
LIMIT 50
Of course, the first thing I did after I found this out was limiting the revisions in WP by putting this line in wp-config.php
define('WP_POST_REVISIONS', 2);
I highly recommend doing this for content-heavy WordPress sites.