WordPress SQL Queries for database cleanup


With time your WordPress database may grow out of control. A couple contibuting factors are post revisions, RSS feeds fed through the dashboard. There are plugins that handle basic database optimizations, however, the command line is a quick and dirty way to handle some of these.

I have always been aware of the transient RSS entries in the wp_options of the database. So the first thing I do is disable this from the dashboard. However, several of these entries made their way into the database. I used the following command to delete these entries from my database. It resulted in a savings of over 450 KiB.

Remove the transient_rss feed data

DELETE FROM `wp_options` WHERE `option_name` LIKE "_transient_timeout_rss%";
DELETE FROM `wp_options` WHERE `option_name` LIKE "_transient_rss_%";
DELETE FROM `wp_options` WHERE `option_name` LIKE "_transient_timeout_feed_%";
DELETE FROM `wp_options` WHERE `option_name` LIKE "_transient_feed_%";

Find unused tags

SELECT * From wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

Remove unused tags

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

I have tested and used the above queries. The source below offers these and many other great SQL queries.