If you happened to glance at my previous post about the media_sideload_image function you’ll know that recently I had to manually import a load of content into a WordPress install. A lot of this was done with manually MySQL queries as oppose to using the built in WordPress functions for performance reasons. Once we’d done the actual data importing we wanted to tidy up the database and the content a bit, below are a few MySql queries we ran that you may find useful at some point.

n.b.Backup your database before running any sort of queries

Reset WP Posts & Tags count

After we’d imported all our posts, tags, and linked them up we noticed that the count for the amount of posts belong to a tag was out. This was because we’d imported them using custom functions. The query below will update your tag posts count.

UPDATE wp_term_taxonomy
SET count = (
  SELECT COUNT(term_taxonomy_id)
  FROM wp_term_relationships
  WHERE wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
  GROUP BY term_taxonomy_id
);

Tags without Posts

We also found we had a lot of superfluous tags that weren’t attached to any posts that had been created by the content managers. It would be much cleaner if these were removed.

View tags without posts

SELECT * FROM wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id
  WHERE wtt.taxonomy='post_tag'
  AND ( SELECT COUNT(term_taxonomy_id)
    FROM wp_term_relationships
    WHERE wp_term_relationships.term_taxonomy_id = wtt.term_taxonomy_id
    GROUP BY wtt.term_taxonomy_id ) IS NULL;

Delete tags without posts

DELETE FROM wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id
  WHERE wtt.taxonomy='post_tag'
  AND ( SELECT COUNT(term_taxonomy_id)
    FROM wp_term_relationships
    WHERE wp_term_relationships.term_taxonomy_id = wtt.term_taxonomy_id
    GROUP BY wtt.term_taxonomy_id ) IS NULL;