Shrink WordPress Database Usage


My FTP provider (fasthosts) only allows 150MB maximum usage per database created. This is big enough for most personal usages. However, the other day, after I logined to the control panel and I noticed that the database I use to create my blog (this site) has already taken up to 23% percentage usages. I haven’t got many posts although.

This is definitely a potential problem. I don’t want to pay for space upgrade when it reaches 150MB so soon. And the following is a quick way to shrink the database usage taken by wordpress site. In my case, it has been shrinked to 3% after the following steps.

1. Delete Revisions

For most wordpress sites, the function ‘revisions’ for posts is completely useless. A Revision is a ‘snapshot’ of post so that you can recover to any time when you make a mistake. The revision surely grows if you have many posts and they are just there taking up space.

Make sure you backup your database first and execute the following SQL (for example, using PHPAdmin).

DELETE FROM wp_posts WHERE post_type = "revision";

2. Disable Revisions

To save the future hassle, if you don’t ever use ‘post-revision’ function, you can just disable it by adding the following line in the wp_config.php under the WordPress installation folder, after the following line.

1
2
3
4
define('DB_COLLATE', '');
 
// add this.
define('WP_POST_REVISIONS', false);
define('DB_COLLATE', '');

// add this.
define('WP_POST_REVISIONS', false);

3. Delete Spam-comments (and also unapproved)

In my case, the wp_comments table consumes lots of space than wp_posts. This is simply because many spam spiders will post comments to your site and in my case they will be captured as spam-comments without notifying you. Days after days, your database will grow.

Use the following SQL command to delete every comments other than ‘approved’. Make sure you review every ‘un-approved’ comments first.

delete from `wp_acmcomments` where `comment_approved` <> 1

Adjust the where statement if you only want to delete the spam comments.

You can put above solutions in a crontab that executes regularly, for example, daily. And you are suggested to executed optimize table after purging corresponding table.

–EOF (The Ultimate Computing & Technology Blog) —

GD Star Rating
loading...
441 words
Last Post: Unspecified Error in Delphi 2007 on Windows 8
Next Post: A Simple Math Problem

The Permanent URL is: Shrink WordPress Database Usage

One Response

Leave a Reply