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
I highly recommend doing this for content-heavy WordPress sites.