Clean Up the wp_posts WordPress Table

For every post that you write or page that you create, WordPress stores many revisions that you can go back to. That comfort is fine until one day, you start noticing that your server space is inexplicably hogged. The culprit is the wp_posts table that in lack of restrictions on revisions, grows and grows.

The WordPress wp-posts Revision System

Revisions can be accessed from the sidebar menu on any post or page. You can browse revisions to see what changed and when. If necessary, you can go back and forth between revisions to look for content that you deleted, or revert to an older version. The problem surfaces only when your WordPress blog is inundated with revisions.

 
By default, WordPress stores unlimited revisions for your posts and pages, even if you rarely give them a second thought. However, cleaning the wp_posts table and freeing space can be as simple as a MySQL query. In this article, we will show you how to do it and how to prevent revisions from accumulating in the future.

Cleaning the wp_posts Table from phpMyAdmin

Revisions are stored in your MySQL database, so it makes sense that the first stop to cleaning revisions is the database itself. phpMyAdmin is one of the most common administration tools for MySQL and permits querying in a user-friendly layout.

To remove old revisions, locate and enter the wp_posts table. Up top, choose the SQL tab and enter the below query:

DELETE FROM wp_posts WHERE post_type = "revision";

The query looks for versions of posts that are revisions in the wp_posts table and deletes them. Simple, but extremely effective; in a small WordPress blog, the table size went from 86kb to 22kb - a 75% reduction. In larger websites, it is capable of removing as much as 90% of archived data.

Cleaning the wp_posts Table from WP Speed of Light

Manually removing revisions from the wp_posts table is complex and introduces the risk of erroneously removing actual posts and pages. WP Speed of Light offers a far more intuitive way of cleaning revisions from the wp_posts table.

Old revisions can be cleared from the Database Cleanup tab menu in WP Speed of Light’s configuration. One of the options is clean all post revisions - tick it and press the clean & save button to free your wp_posts table from unnecessary content.

If you have the Pro Addon of WP Speed of Light, you can go a step further. Up top on the same page, you can enable automatic cleanup and choose how often you want the wp_posts table to be cleaned. Press clean & save to confirm the changes.

Limiting the Number of WordPress Revisions

Like other things, prevention is better than cure. WordPress’ configuration lets you limit the number of revisions to store for every post or page. To set this option, access your server with SFTP and open up the wp-config.php file.

In wp-config.php file you want to look for the ABSPATH definition line. For any of the below lines to work, you need to insert them before this line of code. There are two ways of limiting the number of WordPress revisions. You can write out the following line of code to retain only the most recent revisions:

define('WP_POST_REVISIONS', 5);

Naturally, you can change the number to specify your own limit on posts and pages revisions. Alternatively, if you want to store no revisions at all, you can disable WordPress’ revision system altogether by writing the following code:

define('WP_POST_REVISIONS', false);

On paper, WordPress revisions can be a blessing, allowing you to hop around older posts and pages to find content that you have edited out. In reality, the space limitations on small servers can quash out the usability of revisions and turn them into cumbersome archives. Whether with WP Speed of Light or manually removing them, revisions do not have to be a hindrance, and going forward, you can change their behavior to suit your needs.

It is also good to note that WP Speed of Light will allow us to analysis our site speed and also give us simple options to quickly optimize it as you can see here.

More Details: WP Speed of Light >>