If you look into the WordPress database, table wp_posts, you’ll see lots of records … much more then the actual displayed posts and attachments combined. The reason lies in revisions.

Among others, by default, every post save creates a revision. For the blog, I’m managing, the statistics are: 809 posts and 6287 revisions. And I must mention that I already deleted the revisions for first 391 posts. That means above 15 revisions per one post.

This does not present a big problem if you do not use a plugin such as Yet Another Related Posts Plugin. These related-posts plugins create a fulltext index on post title and content. And that means, if you want speedy searching for the related posts, big indexes – in my case 55 MB. And of that size, 75% is never used. And if didn’t have deleted some revisions before, the MBs and percentage would be even higher. And on webhosting servers this can present a problem because of high memory usage.

Unfortunately MySQL does not (yet) support partial indexing with which you could disable indexing of the revisions. Because of this revisions must be deleted. Some plugins are addressing this problem, but I was unable to find one, which deleted records older than certain number of days. It’s only all or nothing.

Searching the web (I’ll include the link if I stumble upon it again) I found a deletion code and added a time filter (use at your own risk):

DELETE p, tr, pm
-- SELECT p.*, tr.object_id, pm.meta_id
FROM wp_posts p
JOIN wp_posts pp ON pp.ID = p.post_parent
LEFT JOIN wp_term_relationships tr ON tr.object_id = p.ID
LEFT JOIN wp_postmeta pm ON pm.post_id = p.ID
WHERE p.post_type = 'revision'
AND pp.post_type = 'post'
AND pp.post_status = 'publish'
AND p.post_date < ADDDATE(CURDATE(), INTERVAL -2 MONTH)

This can then be used to make a cron job (Linux) or schedule (Windows) to periodically delete old revisions. Linux example: 'mysql --user=? --pass=? --execute "INSERT CODE HERE" DATABASE'.

With WordPress 3.0 a feature called Featured Image was introduced. It enables the poster to add a thumbnail to his post. But if you have written lots of blog entries with an older version of WordPress and you still want to display thumbnails on them, you’ll have to find sine other solution, if you don’t want to add thumbnails to all of them.

You can, of course, use a freely available plugin such as Thumbnail for Excerpts. But it has one flaw. It only accepts/generates thumbnails with static width and height. If you have a variable lengths of picture’s axises (let’s say 100×100, 75×100, 100×75) the plugin will detect only the boxed (and cropped one).

To get around this – to use the excerpt and Feature Image – the plugin (the example refers to version 2.1) has to be corrected a bit.

Add function into “thumbnailforexcerpt.php”:

function tfe_get_attached_image($id,$align) {
  $images = get_children("post_parent=$id&post_type=attachment
    &post_mime_type=image");
  if($images) {
    $image = min(array_keys($images));
    $src = wp_get_attachment_thumb_url($image);
    $alt = get_post_meta($image,'_wp_attachment_image_alt',true);
    $titleA = get_post($image, ARRAY_A);
    $title = $titleA['post_title'];
    return '<img width="'.$width.'" src="'.$src.'
      " alt="'.$alt.'" title="'.$title.'" />';
  }
}

And in the first line of the function “tfe_get_image”, write “return tfe_get_attached_image($id,$align);”.

What does the code do? It finds all images, attached to the post through Media Library. Then it reads the thumbnail for the first attached image and extracts the ALT and TITLE tag. The size of the thumbnail is stored alongside the image, so there is no need to guess it’s size.