Find Posts with the Most Number of Revisions

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.