WordPress Post Revisions – Delete and Limit

re: WordPress 3.2.1, phpMyAdmin 3.3.10, MySQL 5.1.54

WordPress by default will store an unlimited number of revisions made to a post and allow you to restore any of these versions. The revision history and the ability to restore these is a great feature of WordPress – but – it can create database-bloat. The extra space taken up by a lot of revisions may be a concern to some as well as there may be minor performance improvements when reducing the size of your WordPress database.  I’ll follow with some steps to do some maintenance on your WP database but first there is a nice WP constant you can define to control revisions.

I’ll not suggest turning off revisions but you can limit the number of revisions created where older ones will automatically be deleted when you reach the limit.  To limit revisions to 2 edit your wp-config.php file and add:


Turn off revisions by setting the constant to false or 0 (not recommended). 

Just in case you aren’t 100% familiar with WP’s revisions you can see these when editing a post in admin, scroll down to the bottom, and you’ll see a “Revisions” section. Clicking on the link provided will allow you to view and restore that revision. Note that revisions are not created from WP’s auto-save feature.


If you aren’t seeing this then the post doesn’t have a revision or this Revisions section isn’t visible – to turn on scroll up to the top and over on the right you’ll see Screen Options which you can drop-down and make sure Revisions is checked.


Now be warned to go much further you’ll need some experience with phpMyAdmin and/or the shell prompt. And I’m a bit leery of the WP plugins that state to take care of this level of database maintenance but maybe there are some good ones out there. Oh and I’d always suggest backing up your database before doing this level of maintenance.

The example WP site I’m using has about 12,900 posts and from phpMyAdmin select the  WP database and before removing the revisions and optimizing we see the size: 77.3MB,


Now lets get a count of the total number of revisions – run this SQL query,

FROM wp_posts a
LEFT JOIN wp_postmeta b ON ( a.ID = b.post_id )
LEFT JOIN wp_term_relationships c ON ( a.ID = c.object_id )
WHERE a.post_type = ‘revision’


Not a small number of revisions and since we’ve decided we no longer need any of the revisions we’ll delete them all.  Note that while you can just query wp_posts for your total revisions, we’re joining on the meta data also since when we delete the revisions this is the query we needed. Otherwise you’ll end up with orphaned records in wp_postmeta & wp_term_relationships,

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_postmeta b ON ( a.ID = b.post_id )
LEFT JOIN wp_term_relationships c ON ( a.ID = c.object_id )
WHERE a.post_type = ‘revision’


Since we’re removing the needed rows from all three tables the number deleted is higher than our post revision count.  Now after doing this the database size won’t change so we need to optimize it,

To optimize your database you can do this from a command/shell prompt using the myslqcheck command like:

mysqlcheck -o –u user_name –u database_name

Replace user_name and database_name with your values.

You can also do this from within phpMyAdmin – select your WP database, click the Structure tab and click Check All to check all the tables, then from the With selected: dropdown select Optimize table,


The screen shot is post optimize where we’re down to 50.9MB – and nice 1/3 smaller. Of course we’ve lost all revision history in the process.

Internet Explorer v8 and Opacity Issues

A great WordPress plugin jQuery-lightbox (JQLB), used to provide animated slideshow-like viewing of images in a post seemed to be having some undesired behavior on sites viewed with Internet Explorer (IE) v8 – the plugin’s jQuery based javascript was unable to successfully set the opacity of an “overlay” <div> it created in process of showing images. All worked fine in Firefox (FF) and IE v9 but would fail (though not all the time) in IE v8 (unable to test IE v7 but it may also have similar issue). Some more testing seemed to confirm that all versions of the JQLB plugin were affect as were all versions of jQuery, but the issue only affected Windows 7/IE v8. 

Some background: When invoked JQLB’s jQuery javascript will create a <div class=’overlay’>, set its height and width equal to the page size, set the background color to black, and absolutely position it to top:0, left:0 so it covers the entire page. It also sets the <div> opacity to 80% to give a transparent affect so the underlying page is still partly visible.  On some pages and using IE v8 the affect failed and there would be no opacity with only a completely black background.

Continue reading Internet Explorer v8 and Opacity Issues

RPX Plug-in Setup for OpenID Sign-on

[note this post is under going revisions and will be updated]

Some tips on Adding FaceBook sign-on authorization – it takes a number of steps not all of which are well documented on http://rpxnow.com. This post assumes you’ve already installed the plug-in to your site (WordPress in my case) and have it working with the default installed 3rd-party providers (Google, etc…). I’d never created a Facebook App before so I had to allow the Developers App in my account. It then took a while figure out what an API key was and how to get one. After that then finish up the steps for adding Facebook as a sign-on provider back on the rpxnow page.

Continue reading RPX Plug-in Setup for OpenID Sign-on