Find Posts with the Most Number of Revisions

SQL - find post 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
  JOIN wp_posts as children
    ON children.post_parent = parent.ID && children.post_type = 'revision'
  WHERE parent.post_parent = 0
    BY parent.ID
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 wp-config.php

define('WP_POST_REVISIONS', 2);

I highly recommend doing this for content-heavy WordPress sites.

Baki Goxhaj
I’m a web consultant specializing in WordPress. I love elegant solutions, quality code and a good laughter.
You may also like
Easy PHP II: Variables
Why You Shouldn’t Use Tables for Web Layouts

Leave Your Comment

Your Comment*

Your Name*
Your Webpage