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.