WordPress has helpful functions tag clouds, terms, categories and more inside your blog. I find it helpful to be able to monitor my data outside of WP occasionally with direct database queries.
There are some legacy (old) queries floating around the internet regarding the topic, but WordPress has continuously changed over the years making some of them obsolete. These newer queries take advantage of the WordPress 3.x database.
What’s most exciting is the fact that the database now keeps an updated count of your tag/term usage. Whenever an article gets published, it updates the count for the post tags in it. It also correctly handles that count in regards to draft and future published articles.
Solid Tip: These queries use the default ‘wp_’ database table prefix. Be sure to modify if you changed it.
Get Tag Count
Get Category Count
Legacy WordPress 2.x Queries
I did not write these queries, but they are still useful for examining dates.
/* Get all tags and their last usage date */ SELECT name, slug, count(history.tagId) AS total, tagdate FROM (SELECT rel.term_taxonomy_id AS tagid, substr(p.post_date_gmt,1,10) AS tagdate FROM wp_term_relationships rel INNER JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id=rel.term_taxonomy_id INNER JOIN wp_posts p ON p.ID=rel.object_id WHERE taxonomy='post_tag' AND post_status='publish' ORDER BY post_date_gmt DESC) AS history INNER JOIN wp_terms t ON t.term_id=history.tagid GROUP BY history.tagid ORDER BY total DESC /* Get history for every tag */ SELECT t.name, substr(p.post_date_gmt,1,10) AS date FROM wp_term_relationships rel INNER JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id=rel.term_taxonomy_id INNER JOIN wp_terms t ON tax.term_id = t.term_id INNER JOIN wp_posts p ON p.ID=rel.object_id WHERE taxonomy='post_tag' AND post_status='publish' ORDER BY post_date_gmt DESC
The last query can be simply modified to show you the history a single particular tag also.
AND t.name = 'zucchini' between lines 4 and 5. This would pull a list of dates that ‘zucchini’ was used as a post tag.