Clean Wordpress Database removing empty categories

wp-cleanIf 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.

PLEASE NOTE

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)

Table Optimization

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`

Cron it

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.

Andrea Olivato