Deleting post tags removes categories

Question

I'm trying to remove old post tags from a few WP installs. In some instances, I have over 20k tags for a few thousand posts. I've tried the following code:

delete t.*,tt.*,tr.*
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy as tt
INNER JOIN wp_term_relationships as tr
WHERE tt.term_id = t.term_id
AND tr.term_taxonomy_id = tt.term_taxonomy_id
AND tt.taxonomy = 'post_tag'
AND tt.count < 5

This is supposed to delete any tags that are used on less than 5 posts. After I run it, I noticed that a number of categories are deleted as well (taxonomy = 'category') and I'm stumped why? Anyone else managed to do this??

Answer 1

I'm not sure about this, but the way the tables are setup, WordPress can re-use the same terms record (with the same term_id) within different taxonomies. That is, if you have a post_tag called favorites and you also have a category called favorites, it can well be that WP uses the same terms record for both and only diversifies them via the term_taxonomy_id table. There's obvious advantages to that.

So...if your query above finds the term as part of the query and deletes it, then if it is also used as a category, it disappears, too, because the term record is gone and the relationship cannot be drawn anymore.

As said, i haven't verified that WP is actually doing it that way, but it would explain your phenomenon.

Solution would be to only delete term if its id only appears in tt.term_id once. I'd recommend breaking up the query for that.

Answer 2

Thanks for the tip - here is how it ended up and it seems to work a treat :)

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy = 'post_tag' AND count <= 5) and term_id not in (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy = 'category')
DELETE FROM wp_term_relationships WHERE term_taxonomy_id IN (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = 'post_tag' AND count <= 5) and term_taxonomy_id not in (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = 'category')
DELETE FROM wp_term_taxonomy WHERE taxonomy = 'post_tag' AND count <= 5