I was doing some routine backups for this blog when I was a bit surprised by the size of the database backup. I had 345 posts and 105 comments yet my database size was 4.9 megabytes - far larger than I had assumed. This isn't a huge difference and it isn't the end of the world, but it bothered me enough to find out.

As it turns out Wordpress acts a bit like Wikipedia in that it saves all the revisions of each post. So every time you change a post and re-save it, Wordpress keeps the original as a backup and creates a new revised post. My best estimate was that I had 1321 revisions which were unused and cluttering up the database.

Luckily there is one SQL command you can use to clear all the revisions out of the database, it is;

DELETE FROM wp_posts WHERE post_type = "revision";

When I ran it I got the following output;

Query OK, 1240 rows affected (0.43 sec)

So it turns out I had 1240 revisions in my database that I really don't need.

Here is the before file and the after file to show the difference in my database size;

user@localhost:~/backup$ ls -all -h
drwxr-xr-x 2 user user 4.0K 2009-11-26 15:21 .
drwxr-xr-x 8 user user 4.0K 2009-11-26 15:20 ..
-rw-r--r-- 1 user user 4.9M 2009-11-26 15:11 20091125-blog.sql
-rw-r--r-- 1 user user 1.6M 2009-11-26 15:21 20091126-blog.sql

Much better! Now if shrinking the size of my gallery was only that easy...

user@localhost:~$ du -h
...
687M ./gallery
...