WordPress database clean up

I tried to upgrade my blog to WordPress 2.5 days ago so I had the chance to check my MySQL database closely in PhpMyadmin. And I was astonished because the size of my database is over 4MB! That’s huge for a small scale blog. When I checked the tables in the database, I found there are two tables were uncommonly large: wp_comments and wp_options, both are over 1MB or more. When I checked them out by browsing their contents, I found that in the wp_comments table, there are tons of spam comments (marked “spam” in the “comment_approved” field). In the wp_options table, there are lots of unused options from uninstalled plugins left and there are lots of items begin with rss_. With a simple search on the net, I learned those spam comments and trackbacks are stored in the wp_comments table even though they are marked as spam and not posted to the blog. And for those options with rss_ prefix, they are RSS feeds contents used in WordPress dashboard, etc. So basically I don’t need them, especially when they make the database so fat and make the blog significantly slow.

To clean them, I just simply delete the spam comments and trackbacks in wp_comments table and rss_ items in wp_options table. The SQL query is like this:

DELETE FROM `wp_comments` WHERE `comment_approved` = 'spam'

After the clean up, my database is just about 900KB, what a difference! If you are not comfortable to edit in PhpMyadmin directly (like regedit in Windows), fortunately there are some plugins out there do the job well. CJD Spam Nuke and Clear Options are two of them and very easy to use. The latest Akismet plugin also can delete the spams from database manually or automatically.

WordPress database backup and restore

I heard from a Chinese blog that it seems blogs hosted by weblogs.us were blocked by China’s “Great Fire Wall”. I am a little bit astonished because weblogs.us is not a large scale BSP at all. Anyway, it happens.

I ever considered to buy web hosting space and domain name to build my own homepage as well as blog. Now I have to consider it again, though the service offered by weblogs.us is unbeatable at this price ($0, thanks JD!). So I just tried to backup the database at weblogs.us and restore it on my local server as a rehearsal. It works well. With phpmyadmin, I can backup the whole database or any of the tables, then restore it by phpmyadmin on my local server. After restore, I need to browse the “options” table and modify the wordpress URI manually. Then I can access http://localhost/wordpress/wp-admin/. A theme needs to be selected if the theme used on weblogs.us is not installed on localhost yet, otherwise the blog will turns out to be empty. After that I have a mirror site of my blog on local.

Ref: Backing Up Your Database at WordPress.org Codex