Bloated WordPress Database

It all started when Google crawler reported a few long-to-load pages on the site. After some poking around, I finally looked at the database. The wp_options table was three times bigger than the wp_posts table — oops. Seems that the wp_options is also used for the RSS cache — so it eventually bloats.

As a quick fix, you may simply run this query to get rid of the RSS cache entries.

DELETE
  FROM `wp_options`
  WHERE `option_name` LIKE 'rs_%'
  AND`autoload` = 'no'
  AND char_length(`option_name`)  > 25;

After that optimize the table using:

OPTIMIZE TABLE `wp_options`;

In my case, the wp_options table went from 950 kb down to 47 kb; almost 20 times smaller. For finer control of what to delete — and if you do not like SQL — try the WP-Options-Manager plug-in. The manager also allows cleanup of junk data, left behind by inactive plug-ins.

Somewhere around version 2.6, WordPress introduced auto-save and revisions — you may or may not like these. The following query lists all revisions in the wp_posts table.

SELECT *
  FROM `wp_posts`
  WHERE `post_type` = 'revision';

The auto-save and revisions can be disabled using a plug-in. And a final warning — before you start SQL scripts — make sure you have the latest database backup somewhere safe; bad things do happen.

Update:

The database backup dropped from 209 kb to 86 kb — by about 60%.