9 Steps to Clean Your WordPress Database

By (Guest Blogger)
There are 17 comments, join the conversation

Is your WordPress database a mess? Have you been complaining about it since time immemorial? Well, then your WordPress site might be as bloated as you were after that marvelous Thanksgiving dinner at your mom’s. If your site has been live for a while now, chances are that its database has acquired quite a bit of garbage and is demanding a spring clean.

The trick to maintaining a database is to clean it regularly to prevent the accumulation of unnecessary data. However, we are guessing that you have been skimping on the cleaning bit, which is why you are here today. Therefore, without wasting time, we will tell you how to clean up that database in a few easy steps.

#1. Basic Ground Rules

Before you take on the massive task of spring-cleaning, you must ensure that your valuables are safe and sound. Losing a single system file can mess up your website entirely and can cause you to lose thousands of dollars in revenue.

So start by creating copies of important files and create a backup of your entire website. You should definitely check out Backupbuddy from our friends at iThemes. It’s a great plugin that allows you to schedule automatic full or database-only backups, and we highly recommend it.

#2. Screen Through the Clutter

Do you remember how yard sales used to work before Craigslist and eBay came along? Just like separating the valuables from trash, you need to pick out the plugins you use and the ones you don’t.

You can easily get started by going through your complete list of plugins in your WordPress dashboard. Go to your admin panel and click on Plugins. Next, go to Installed Plugins and click on Inactive at the top of the screen.

Eliminating unused plugins should easily free up a couple of bytes and is the first step towards cleaning up your WordPress database.

#3. Time For a Purge

This might sound very ominous (esp. to the ones familiar with the popular movie franchise), but it is the best way to free up space. After deleting all unused plugins you should start cleaning out post data.

If you have been using WordPress from a while, you might not be as surprised to know that all your post data is being stored in your website database. You can find it all in the wp_postmeta table, and you can run this query to get rid of all superfluous data:

DELETE FROM wp_postmeta WHERE meta_key = ‘your-meta-key

Always remember to replace the last part with the value that has to be cleared out.

#4. Take Care of Spam

Spam is no longer restricted to your inbox. If you look through your database carefully, you will find tons of spam comments as well. These include promotional features and unscrupulous backlinks that other sites use to eat their way into your SEO. Monitoring comments is an essential practice that is currently underrated.

The easiest way to manage spam comments on your site is to execute this query:

DELETE FROM wp_comments WHERE comment_approved = ‘spam’;

If you are currently using a multi-site installation of WordPress, you may like to try the following query:

DELETE FROM wp_#_comments WHERE comment_approved = ‘spam’;

#5. What About Comments Waiting on Moderation?

As a WordPress website/blog admin, you have the power to delete all comments that are awaiting moderation. This eliminates the need of unnecessarily going through each and every spam comment that you haven’t yet moderated.

Just a word of advice: go through the list once to approve all genuine comments before running this query:

DELETE FROM wp_comments WHERE comment_approved = ‘0’;

#6. Taking Care of Unused Tags

This has happened to almost all of us. When we were new at blogging, we created a few hundred tags thinking we would use them from time to time. And now that we post regularly, we hardly ever change tags. We use the most common ones and stick to those.

Don’t be surprised if you have completely forgotten about a score of tags that you exist on your website database (thanks to your wild imaginative powers). Fortunately, we have a query that will take care of all unused tags, but be sure to check the ones that you do use before running this query:

DELETE FROM wp_terms wt
INNER JOIN wp_term taxonomy wtt ON wt.term_id = wtt.term_id WHERE wtt.taxonomy = ‘post_tag’ and wtt.count = 0;

Check out some of our earlier posts if you are unsure about the differences between categories & tags and how to use them.

#7. Bid Farewell to Pingbacks

If you are currently using a setting that has turned off the option of accepting pingbacks, then you may want to use the following code. This will remove every pingback ever made to your website from your database.

DELETE FROM wp_comments WHERE comment_type = ‘pingback’;

Again, if you are currently using a multisite installation, you may try using the next one. just remember to replace # with your current site id.

DELETE FROM wp_#_comments WHERE comment_type = ‘pingback’;

#8. No more Post Revisions

Post revisions are indeed necessary for blogs, but once you see the space they take up on your database, you won’t think twice about bidding them farewell. They grow fast and exponentially. You can clear them out at one go using the next query:

DELETE a, b, c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = ‘revision’

You can even chose to disable all post revisions for your website using the following SQL code:

define(‘WP_POST_REVISIONS’), false);

#9. Getting Rid of Trackbacks

Your final step should be getting rid of all old trackbacks. To make sure that nothing goes wrong, double check so that all your pingbacks and trackbacks have been disabled before trying the next SQL code:

DELETE FROM wp_comments WHERE comment_type = ‘trackback’;

For multisite users, the next query should be more profitable. You can use this one to clean out all your trackbacks at one go. Like previous ones, replace # with your current site ID to remove all of your site’s trackbacks.

DELETE FROM wp_#_comments WHERE comment_type = ‘trackback’;

Besides these, you can also scrap your old posts once your audience has fallen out of love with them. This might be difficult at first, but many old posts are nothing but unwarranted burden for WordPress databases.

If you are new and quite lost with the new set of codes, queries and rules we were talking about right here, leave your maintenance to seasoned plugins like Optimize. They work great with multisite installations as well, and include extensive options for managing and removing unused tables.

This article has been written by Derek Iwasiuk from www.engagethecrowd.com.

About (Guest Blogger)

Derek Iwasiuk is a name to be trusted in the world of SEO marketing. He runs a leading national SEO research firm that is highly recommended by successful businesses in the country. You can find Derek at http://wwww.engagethecrowd.com


This post was written by a guest contributor. If you would like to guest post for Semper Plugins please reach out to us via our contact form.
17 comments on “9 Steps to Clean Your WordPress Database
  1. Peter Reema says:

    I use your plugin with our host Webhost Melbourne. They are extermely powerful with instant setup. Keep up the good work.

  2. Jeremy says:

    Thanks so much for the great information!

  3. Steve says:

    Thanks for the article David. This is helpful information. OMG!

  4. Sandra Oshiro says:

    Hoping for some clarification here…this is great info for cleanup in general. But it doesn’t seem specific to my “database”. Maybe this is because my database for electronic newsletter recipients is in a plugin called MailPoet. I seem to have issues with that from time to time. (i.e. people tell me they used to get my newsletter but now they don’t…often times, I can unsubscribe them and re-subscribe them and that solves the issue, but sometimes it doesn’t). Will these steps you’ve shared, that seem to clean up WordPress in general, have any relation to the plug in I use? (sorry if this seems a dumb question – I am fairly new to WordPress and don’t really understand how the plugins relate to the main platform.)

  5. fukuokakatazuketai says:

    Thanks so much for the great information!

  6. Suzanne says:

    Wow, I can see the purpose in deleting barnacles like spam and dozens of revisions, but deleting articles themselves? Why don’t you go tell CNN, Time, USAToday, or all the CBS Locals (all WordPress sites) to just delete their archives, which even just in the last election proved critical for fact checking the lying mainstream fake news sites. I can’t think of a worse recommendation than “delete your old articles”. Without content, you have NOTHING. Those who forget history are doomed to repeat it.

    • Arnaud Broes says:

      You are not supposed to delete all of your old articles, but some users may find it useful to delete older articles that contain outdated content.

  7. jyotsna says:

    Thanks so much for the great information!

  8. jobplaza.it says:

    Useful info. Never done manual database cleaning, I’ve always been using some plugin for maintenance. I think the time has come, will follow the steps in this artlicle after a good backup!

  9. Andi Saputra Siahaan says:

    Wooww, what a good article.. I like it and it helps me a lot to clear up my WordPress database.

  10. Louis Dallara says:

    Great post, I’m new to database work. I have question how does one execute the the camands you have listed? Php My Admin ?

  11. Kutas Gur says:

    My WordPress admin is running very slowly, thank you so much for this useful article. I’m so bored with slowness, I hope I do not brake my site. Maybe it’s best to break it and setup wp again. thnx

    • Arnaud Broes says:

      I recommend you also review your hosting service provider and see if your server specifications meet your demands.

  12. Laurie says:

    I have no idea where I place this and the other commands. I understand it is a good idea but have no idea how to get started.
    DELETE FROM wp_#_comments WHERE comment_type = ‘pingback’;

    Thanks

    Laurie

    • Arnaud Broes says:

      Hi Laurie, these are SQL commands and can be executed in PHPMyAdmin, the software that is used to manage the database of your site. If you don’t have any knowledge on SQL or databases, and have no idea what you are doing, you shouldn’t try to do this as you can seriously damage your site or its content by doing so.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

[i]
[i]
Skip to toolbar