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
JOIN wp_posts as children
ON children.post_parent = parent.ID && children.post_type = 'revision'
WHERE parent.post_parent = 0
ORDER BY children_count DESC
Of course, the first thing I did after I found this out was limiting the revisions in WP by putting this line in
I highly recommend doing this for content-heavy WordPress sites.