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.

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:

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

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 Media Cleaner 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.

dui.jpg

Similarly, you should run this plugin every few months to clean 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” button to start the optimization. It should take less than a minute.

optimized-db.jpg

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.

schedule-optimize-db.jpg

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.

clean-option.jpg

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.

broken-link-checker

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.

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?

Damien Damien

Damien Oh started writing tech articles since 2007 and has over 10 years of experience in the tech industry. He is proficient in Windows, Linux, Mac, Android and iOS, and worked as a part time WordPress Developer. He is currently the owner and Editor-in-Chief of Make Tech Easier.

10 comments

  1. Wow… thanks for your plugin information.. its very optimize my blog. Thanks man..

    Do you know some plugin to auto optimize database with schedule every hours..?? I need this plugin… Please tell me if you found function of plugin like that..

    Thanks

    Ferri
    Marketer Indonesia

  2. Is there a reason why you prefer delete-revision over wp-optimize to delete post revisions?

    1. I prefer deleted-revision because it is a simple plugin that can be easily modified to delete all unused entries related to the revision.

  3. Hey ,

    I just found you out while googling , and this a really nice blog. :)
    I the tips were really useful . I will be using them . Recently had trouble with unused images.
    Expect me to be a frequent visitor here. Will read all your posts
    Thanks ,

    Regards ,
    Sourish

  4. For what it’s worth, I made the suggested modification of delete-revision.php and came up with unsatisfactory results. Specifically, it took out some custom sidebars that I’d created. I restored the database, reloaded the delete-revision plugin as originally written, and performed the delete-revision again. This time it did not remove my custom sidebars.

    Here’s the particulars about the wordpress installation I was using:
    WordPress version 2.8.4
    delete-revision version 1.3.1
    Theme: Atahualpa version 3.4.2 (This is what allows the creation of custom sidebars)
    MySQL version 5

  5. It's a great piece, I worked through the list and cleaned out a ton of junk from my site. Along the way I ran into problems with Clean Options deleting options that were still in use – nothing major but perhaps it is worth warning people of the danger.

  6. Thanks for this wonderful tip. I was also having some blog issues where I encounter frequent 404 errors and I doubt that is has something to do with my mySQL database having this issue.

Comments are closed.