Working with WordPress 2.6 onwards, it includes many wonderful administrative features like keeping track of previous slugs so that even if you change your post’s URL, users who accessed your post via the old link will be automatically redirected to your new link. Also, you will be unlikely to lose any of your work or changes due to version control (saving of revisions) and the autosave functions. Unfortunately this comes at a price of the rapid growth of your database, especially the wp_posts and wp_postmeta tables, which are tables containing data accessed the most when outputting content in your site’s pages. You can imagine how over time, as your database grows into thousands of entries, this will increase loop iterations, data retrieval and in effect page load time.
While building aquanelle.com I kept a close eye on my database and wherever I spotted uncharacteristic data or content that I did not know the purpose of, Google and the /* */ PHP comment syntax came in very handy. If you too want to maintain a cleaner and a smaller database for your WordPress site, below you will find information that will allow you to decide if you need those extras or why you might just not; methods for cleaning up the redundant entries in your db if your site has been running for quite some time, as well as preventative measures that will eliminate the cause of this excessive and repetitive data.
Before I begin, I just want to describe a few things for the less technical. If you know how to change your WordPress PHP files or change things directly in your database then you can skip the next two paragraphs.
PHP editing
If you already had the experience of uploading plugins and customising themes, you will see that apart from around 25 files sitting in the root directory where you installed WordPress, everything is contained within three folders: wp-admin, wp-content, and wp-includes. When I mention editing the WordPress PHP files, I will tell you their location starting from the root as some files may have the same names but are within different folder structures. I assume you already have a basic knowledge of editing code, uploading/downloading files via FTP and have access to a web editing program like Dreamweaver. There are many tutorials on setting up and configuring WordPress but if you still need some advice, let me know and I will do my best to help you.
Database editing
phpMyAdmin is one of the most common database lookup and editing tools. Most website control panels provide this. Wherever I give you some SQL to run, you can do so using the SQL tab within phpMyAdmin - just make sure you have selected the right WordPress database if you have more than one. If you click through the table names, you can see all your data and actually see how much of it is duplicate, which is what I am trying to eliminate. If you want to learn more about phpMyAdmin, you can find some more info here, here and here.
Now to database stripping … 
Post Versions (revisions)
One of the features implemented into WordPress 2.6 is the cumulative saving of post versions. Rather than saving changes by overwriting the existing data for a particular page or a post, each version is saved as a separate entry with ‘revision’ reference in the wp_posts table. You might want to keep track of all the changes you make to all your posts but if you create a draft and then keep coming back to it to perfect it (like I do), you’ll end up with way too many of such revisions … and may I just add that your first version gets saved even after writing the initial title because the slug is then updated and saved. So what if you add a full stop to a sentence? Just have a look in the wp_posts table of your db.
In WP 2.6 (and 2.7), there is no option to turn this feature on/off. It can be done by inserting one line of code into your wp-config.php file (thanks Lester Chan).
wp-config.php can be found in the root. Get this file and write the following on a separate line (I put mine right after define ('WPLANG', ''); ). [cc lang="php" line_numbers="false" theme="ccNone"]define (’WP_POST_REVISIONS’, false);[/cc]
If you would like to keep a limited quantity of revisions, just set the number in the code below to your desired limit.
[cc lang="php" line_numbers="false" theme="ccNone"]define (’WP_POST_REVISIONS’, 5);[/cc]
Upload this config file back up and you’re done with revisions! By the way … when you are upgrading your WordPress, this config file will not be overwritten because it contains your db connection info so you will not loose any of these changes.
Deleting all revisions from the database
Here is a little code (thanks Andrei) to run on your database which will delete every single reference to a post revision including the meta data and any relationships associated with it.
[cc lang="sql" line_numbers="false"] DELETE p, tr, pm FROM wp_posts p LEFT JOIN wp_term_relationships tr ON (p.ID = tr.object_id) LEFT JOIN wp_postmeta pm ON (p.ID = pm.post_id) WHERE p.post_type=’revision’; [/cc]
Post Autosave
While you are editing a post, the autosave simply saves your post at a set interval. It doesn’t increase the post count much because each version overwrites iteself so you will never have more than one autosaved version per post. But if you experience problems or find it annoying, inserting the lines of code below into the wp-config.php page will disable them completely.
[cc lang="php"] function disableAutosave() { wp_deregister_script(’autosave’); } add_action( ‘wp_print_scripts’, ‘disableAutosave’ ); [/cc]
You can also adjust the time between each automatic saving of your post. The interval is set in miliseconds so the code below will set your autosave interval at 60 seconds. ‘3000′ will set it at 5 minutes (5min = 300sec = 3000milisec).
[cc lang="php" line_numbers="false" theme="ccNone"] define (’AUTOSAVE_INTERVAL’, 600); [/cc]
Deleting all autosave references from the database
Just like with the revisions above, here’s a little SQL to get rid of all the autosaves.
[cc lang="sql" line_numbers="false"] code goes here … [/cc]
Plugin to eliminate Revisions and Autosave
Wordpress 2.6 plugin:
Disabling both the post revisions and autosave can be done through Exper’s plugin. Although it is 2.7 compatible, the plugin listed below gives you more options.
Wordpress 2.7 plugin:
There is a very neat plugin that allows for a lot of control over the post writing panel as well as the revisions and autosave functions. Go to the WP-CMS Post Control.
Edit Lock and Edit Last
I saw some repetitive _edit_lock and _edit_last entries in the wp_postmeta table. Made me wonder …
What is their purpose?
The _edit_lock and _edit_last gets written every time you open a post for editing. They hold information about who is currently editing a post and keep it locked so that no one else can access it at the same time.
Are they necessary or are they just going to clog up my database?
If you have multiple content editors who have access to the same posts, this is useful for access control so that no more than one person is allowed to open a single post at the same time. When you open a post, the _edit_lock gets written with the post’s reference into the post_meta table. If you disable this function there will be no record set of you opening a post and another will be able to open and edit the same post and overwrite any changes you may be making. If however you are the only editor of your site, then you probably don’t need this because you will not be likely to edit the same page from two different locations at the same time.
How can I delete those little mongrels from my database?
You can run the following SQL script that will delete every reference to _edit_lock and _edit_last. Make sure you have the correct database open.
[cc lang="sql"] DELETE FROM wp_postmeta WHERE meta_key = ‘_edit_lock’; DELETE FROM wp_postmeta WHERE meta_key = ‘_edit_last’; [/cc]
How can I prevent them from being inserted?
If a periodical running of the above script is not sufficient for you, then a little commenting out of some php code will stop this extra data from being put in your database. There are two functions that write and use the _edit_lock and _edit_last. They are: wp_set_post_lock and wp_check_post_lock. You only need to comment out something inside the wp_set_post_lock.
- Get / open this file:
wp-admin/includes/post.php - Do a search for “function wp_set_post_lock“
- Within this function, comment out the following four lines:
[cc lang="php"] if ( !add_post_meta( $post->ID, ‘_edit_lock’, $now, true ) ) update_post_meta( $post->ID, ‘_edit_lock’, $now ); if ( !add_post_meta( $post->ID, ‘_edit_last’, $current_user->ID, true ) ) update_post_meta( $post->ID, ‘_edit_last’, $current_user->ID ); [/cc]
I hope this so far helps but I am still developing this site so more info will be added as I discover new things …