If you own a very large WordPress installation like the one I’m dealing with on LinuxFeed, you probably can not manually look for empty categories and remove them from the admin panel.
With 13.000+ posts and 14.000+ categories it’s quite impossible to use WordPress GUI for management, and great plugins like WP-Optimize or Optimize DB might help but they do not look for empty categories. So I managed to create simple queries to select and then remove all the unused ones.
Be sure to understand that by category I mean everything : posts category, posts tag and links category. All of them, if empty, will be removed.
After performing the deletion, it’s not possible to undo. Please be sure to backup all data before proceeding. Note that this worked for me on WordPress 2.8.5, and might not work with other versions. Proceed at your own risk
LIST EMPTY CATEGORIES
From your PhpMyAdmin installation or via MySql command line tool try running this query.
SELECT x.count AS howmany, t.name AS name FROM wp_terms AS t, wp_term_taxonomy AS x WHERE t.term_id = x.term_id ORDER BY x.count
The result given should list all your categories, together with the number of posts contained. Furthermore the result is ordered by the number of posts, so that empty categories should be at the very top.
If you just want to output empty cat, you can use the WHERE statement like shown below
SELECT x.count AS howmany, t.name AS name FROM wp_terms AS t, wp_term_taxonomy AS x WHERE t.term_id = x.term_id AND x.count = 0 ORDER BY x.count
In my case I got 1,252 result which is the 8.78% of my categories.
Showing rows 0 – 29 (1,252 total, Query took 0.0110 sec)
Del Them All
If you double checked that their are really empty, you can proceed by deleting everything listed before. Here’s the query
DELETE t, x FROM wp_terms AS t, wp_term_taxonomy AS x WHERE t.term_id = x.term_id AND x.count = 0
As this query does delete both the category naming and its taxonomy table, you should have deleted nearly 2 times the number of rows returned by the previous select. In my case
Deleted rows: 2503 (Query took 0.8415 sec)
After such a Delete operation it is good to optimize the affected MySql tables, both to free overhead space and to improve performances. Even if you can perform such operation directly from PhpMyAdmin gui, below is the Sql code to run the optimization.
OPTIMIZE TABLE `wp_terms` , `wp_term_taxonomy`
Probably it is a good practice to keep your tables clean, maybe running this procedure as a Cron. I set up a simple job, deleting all empty categories every Monday at 3.00 AM. you should set up it to better suite your necessities.