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%.