Code

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
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.

Baki Goxhaj
I’m a web consultant specializing in WordPress. I love elegant solutions, quality code and a good laughter.
You may also like
infinit next previous post looping
Infinite next and previous post looping in WordPress
How to Install and Configure Git on Ubuntu

Leave Your Comment

Your Comment*

Your Name*
Your Webpage