Recently we were working with a site that had over 5,000 woocommerce tags, most of which were either empty or had only a few items per tag. The number of taxonomies overall (tags, categories, and attributes) was significantly slowing down the site, so we decided to remove the product tags that weren’t popular because as the saying goes, “20% of the effort gets you 80% of the results!” We decided that the visitors to the site would benefit from seeing the most used tags, so we would keep them.
How to Remove Product Tags in Bulk
After poking around on the web, we found this post which, while incomplete, at least got us set off on the right track quickly. The code necessary to remove the woocommerce product tags only is as follows:
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count < 15 AND taxonomy = 'product_tag'); DELETE FROM wp_term_taxonomy WHERE count < 15 AND taxonomy = 'product_tag'; DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
As you can see, we opted to remove product tags that were being used less than 15 times. If you want to remove only empty product tags, you can change the “< 15” to “= 0” instead. Make sure to change it on both lines though!
The first statement deletes all the terms in the term_id table where the count is less than 15 which is gotten from the wp_term_taxonomy table. The second statement actually removes the data from the wp_term_taxonomy table itself. The third statement then removes all of the relationships that existed between those terms.
IMPORTANT: After running these queries, make sure you optimize your database tables to keep your database running efficiently!
How to Remove WooCommerce Attributes in Bulk
The same code, slightly modified, can be used to bulk remove attributes from woocommerce as well. To do this, we just need to replace ‘product_tag’ with the the name of the attribute wrapped in single quotes, so for example:
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count < 5 AND taxonomy = 'pa_color'); DELETE FROM wp_term_taxonomy WHERE count < 5 AND taxonomy = 'pa_color'; DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
In this example, we are removing the Color attributes that have less than 5 posts in them.
And there you have it. Any questions / comments, leave them in the comments.
Thanks for this, MarketerMatt! I was having a bear of a time removing extensive category association in a product parts database. I tweaked your code to target specific category ID’s, and after backup up the database ran the SQL w/o issue. Is case anyone else is interested my edit looked like this:
DELETE from wp_terms WHERE term_id IN ( n1, n2, n3, n4… );
DELETE from wp_term_taxonomy WHERE term_id IN ( n1, n2, n3, n4… );
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
Note: The n1, n2, n3, n4… refer to the IDs specific to the categories you want to delete. Also, the 3rd SQL command required no modifications.