Skip to content

Fixing Term Counts & Removing Unused Terms in WordPress with MySQL

I came across an interesting problem today.  I’d deleted hundreds of posts, and associated postmeta, from my WordPress database directly in MySQL.  The problem was, if I looked at any of my site’s taxonomies in WP-ADMIN, the terms still had counts listed next to them even if all the related posts had been deleted. I needed to find a way to delete all the unused terms.

Here’s how I accomplished this:

Step 1: Delete Term Relationships

I had already deleted all posts and postmeta, but the links between those post IDs and my taxonomies still existed.  The first step was to clean this up.

DELETE FROM wp_term_relationships
WHERE object_id NOT IN (SELECT ID FROM wp_posts);

Step 2: Update the Term Counts

The next step is to update the term counts for each taxonomy.  In deleting the hundreds of posts that I did, many of my terms would now be used 0 times, but others would still have at least one post in them.  This statement will clean all of that up.

UPDATE wp_term_taxonomy tt
SET count = (SELECT count(p.ID)
FROM wp_term_relationships tr
LEFT JOIN wp_posts p ON p.ID = tr.object_id
WHERE tr.term_taxonomy_id = tt.term_taxonomy_id);

Step 3: Delete Unused Terms

Finally, I ran the following statement to delete all terms that were no longer in use ( they have a count of 0 )

DELETE wt
FROM wp_terms a
INNER JOIN wp_term_taxonomy b ON a.term_id = b.term_id
WHERE b.count = 0;

Conclusion

Note that you may have to delete rows from additional tables depending on what plugins you have running on your site.  The above code is only concerned with the tables that come with a default WordPress installation.

7 Comments

  1. maxwe maxwe

    hi, please help, at the last step, it gives me an error
    unknown table wt in multi delete
    can you help please

    • Alison Alison

      I think the correct code for the last step is:
      DELETE a.*
      FROM wp_terms a
      INNER JOIN wp_term_taxonomy b ON a.term_id = b.term_id
      WHERE b.count = 0;

      • CK CK

        YOU SAVED MY LIFE!!! After wordpress Update to 5.2.4 some of the tables got messed, so plugins wasnt working properly. Apache gave an error in formatting.php coudnt convert somethinf to sring and got WP_Error.

        After this sql cleaning everything works!

  2. andy warhall andy warhall

    yes. me too

  3. Jel Jel

    Same problem here, can you please help out?

  4. Beware terms can be and are used to store relationships with objects outside wp_posts, eg users or buddypress groups.

    Before doing the above check that you don’t have a plugin that does this, otherwise you will be losing important data!!!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.