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

MYSQLview code 
SELECT term.name, term.slug, tax.count FROM wp_term_taxonomy tax 
LEFT JOIN wp_terms term ON term.term_id = tax.term_id WHERE tax.taxonomy = 'post_tag' 
ORDER BY tax.count DESC

Get Category Count

MYSQLview code 
SELECT term.name, term.slug, tax.count FROM wp_term_taxonomy tax 
LEFT JOIN wp_terms term ON term.term_id = tax.term_id WHERE tax.taxonomy = 'category' 
ORDER BY tax.count DESC

Legacy WordPress 2.x Queries

I did not write these queries, but they are still useful for examining dates.

MYSQLview code 
/* 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.
Simply add AND t.name = 'zucchini' between lines 4 and 5. This would pull a list of dates that ‘zucchini’ was used as a post tag.