Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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??

share|improve this question
add comment

2 Answers 2

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.