8 Useful Tricks To Clean Up & Streamline Your Wordpress Database

Being a database-driven software, the performance of your WordPress blog is closely related to how efficient your database is. The bigger and messier your database is, the slower is the performance of your blog.

If you have not been doing regular housekeeping and database maintenance, either because you don’t know how to do or you are just plain lazy, here are 8 useful tricks that you can use to clean up your WordPress database easily and quickly.

1) Turn off/delete post revision

While the post revision is a good way to make sure that any changes to a post is documented, it can be a waste of database and server resources if you are the only user of the blog and does not collaborate with other authors for article writing.

To prevent the post revision from cluttering your database, it is best to turn it off once and for all.

You can easily turn off post-revision by adding the following line to the wp_config.php file

define(‘WP_POST_REVISIONS’, false);

If you want to keep the post revision for record purpose, you can limit the number of revisions saved per post. In your wp_config.php file, add the following line instead:

define(‘WP_POST_REVISIONS’, no_of_revision);

and replace the no_of_revision by the number of revision copy you want to keep.

Removing existing post revisions

Turning off the post revision feature only restrict WordPress not to keep any revision for future posts. It does not delete the existing post revisions in your database. To delete all instances of post revision and all its related entries), we will make use of the delete-revision plugin with some simple modification (the plugin only delete the post revision. It does not delete all other unused entries that are related to the post revision).

Download the plugin delete-revision to your desktop.

Extract the zip file. Open up the delete-revision folder and load the delete-revision.php file in a text editor. Scroll down to the line 140 where you see

$sql = “DELETE FROM $wpdb->posts WHERE post_type = ‘revision’”;

replace it with

$sql = “DELETE a,b,c FROM $wpdb->posts a LEFT JOIN $wpdb->term_relationships b ON (a.ID = b.object_id) LEFT JOIN $wpdb->postmeta c ON (a.ID = c.post_id) WHERE a.post_type = ‘revision’”;

(Code adapted from Lester Chan)

Save the file. Upload the whole delete-revision folder to your blog plugins folder.

Activate the plugin. Go to the Delete-Revision page under the Setting section. Run the application to scan your database for all instances of post revisions and delete them from your database.

If you are keeping copies of post revision in your database, you should run the Delete-Revision manager once every few months to clean up your database.

2) Delete unused images

Everytime you upload an image, WordPress automatically create duplicate copies of that image and resize them to thumbnail and medium size. Most of the time, these images are not used. In addition, there could also be some images that you have uploaded, but did not use them in the post. These unused images could easily add up to a massive size and cause unnecessarily burden to the server and database.

The DUI (Delete Unused Images) plugin scans your WordPress blog and find those images that are not used or referenced to by any of the posts/pages. It then proceeds to delete them from the database/server.

To use the plugin, you have use a FTP program to chmod the Uploads folder (the folder where images are stored) to 777. Once you have deleted the unused images, chmod the folder back to 755 again to prevent access from other people.

Similarly, you should run this plugin every few months to clear up your database.

3) Optimize database

As you add or remove entries into the database, pocket of spaces are created. These are useless space and could cause your database to swell quickly. You can easily free up these spaces by running a SQL optimize table query in myphpadmin.

If you are not technically inclined or find it troublesome to log into your myphpadmin, the best way is to use the plugin WP-DBManager to manage your database.

Install and activate the plugin. Under the Database -> Optimize Database options, select the table that you want to optimize and click the Optimize buttonto start the optimization. It should take less than a minute.

You can also schedule it to optimize the database every few days/weeks/months

Go to Database -> Database option. Scroll down till you see the Automatic Optimizing of DB. Set the frequency for it to auto-optimize your database.

4) Delete unwanted plugin

If you have decided that you have no use for a particular plugin, deactivated them and delete them from your server. The more (unused) plugins you keep in your server, the longer it takes for WordPress to generate the plugin list and the higher tendency that it will slow down the loading time of your blog.

5) Uninstall plugin completely

When you deactivated/uninstall a plugin, the options or the tables that the plugin created are not removed. As you install and uninstall a lot of plugin, there will be more and more orphaned options and tables that are taking up precious space in your database.

For plugins that have created table entries in the database, you can use the abovementioned WP-DBManager to drop the respective table from your database.

For those plugins that have created options, you have to use the Clean Option plugin to hunt down the orphaned options and delete them from the database.

6) Remove unused themes

Similarly, if you have plenty of unused themes in the server, delete those that you don’t use.

7) Check your site for deadlinks

While it is an usual practise to link to other posts/blogs/domains in your articles, you should check frequently that the linked post/domain still exist and you are not left with a dead link. It can be very frustrating for your reader to follow your link only to find that the destination page is no longer around. In addition, too many dead links can also affect your search engine ranking. I am sure this is not something that you want to see.

The broken link checker plugin checks your new article for broken links as soon as it is published. It also scans your old posts when you are logged into the Admin page. Any broken links found are reported to you and you can choose to edit or unlink them from the posts.

8) Delete spam comment

It is common to receive tons of spam comments, sometime as soon as you post the article. Even though these spam comments are not shown in your blog, they are still gathered in your database to educate “intelligent” anti-spam plugins. If you received plenty of spam every day (or every hour), this could be taking up a lot of valuable database space. To erase the spam, either go to the Comment -> Spam page, check all the spam comments and select Delete from the dropdown box.

Alternatively, you can use the WP-Optimize plugin to clean up all spam and unapprove comments

The WP-Optimize plugin also comes with other features such as delete revision, optimize database table and change your username. Personally I would prefer to use the WP-DBManager to optimize my database table as it allows me to schedule the optimization task on a regular basis. In addition, the WP-Optimize is only tested for WP2.7, so if you are still using a WP version less than 2.7, you have to use it at your own risk.

What other ways do you use to clean up your WordPress database?

Tags: database, WordPress, wordpress plugin