MySQL Queries for WordPress

MySQL is what runs behind 99.9% of all WordPress installs so with a little bit of SQL knowledge and some creativity, you could do some great stuff to grab all sorts of data directly from your WordPress database. This post will show you five interesting and quite useful SQL queries to run against your WordPress database.

If you’re not too comfortable with MySQL, you probably shouldn’t run any queries on your production database although you’ll see that all of them are quite simple SELECT statements that aren’t as dangerous as DELETE or UPDATE.

You can use software like MySQL Workbench and phpMyAdmin to run these queries, although more advanced users would prefer going with the MySQL command line client or feeding them directly to $wpdb to get the insights to the WordPress Dashboard.

Monthly Posts Rate

The following query will give you an insight on how many posts have you published every month. This is great if you’re tracking your overall progress and statistics in a spreadsheet, plus it can show you the overall trends, especially if you’ve got more than one contributor.

Play around with the post_type, post_status, perhaps add a post_author field and see how different authors on your blog compare. Very handy indeed, isn’t it?

SELECT CONCAT_WS(', ', MONTHNAME(`post_date`), YEAR(`post_date`))
    AS `post_month`, COUNT(*) AS `post_count`
FROM `wp_posts`
WHERE `post_type` = 'post' AND `post_status` = 'publish'
GROUP BY `post_month`
ORDER BY `post_date`;

WordPress MySQL Query

Names and E-mails of Everybody Who Has Ever Commented

This query will give you a full list of all the people who have ever commented on your blog. It also filters out unapproved and spam comments as well as pingbacks. You can further export this data into CSV and feed it to services like Facebook and Twitter in order to connect and follow all your blog readers. And if you do, don’t forget to update the list every once in a while!

SELECT `comment_author`, `comment_author_email`
FROM `wp_comments`
WHERE `comment_approved` = '1' AND `comment_type` = ''
GROUP BY `comment_author_email`;

WordPress MySQL Query

Monthly Comments Rate

A very useful query indeed, shows the overall monthly trends of commenters on your blog. If you see it growing every month — good. If you don’t, try engage more with your readers and see how the figures change over the next few months.

You can add an additional AND statement to filter out your own comments by e-mail or user id, which will hopefully give you even more accurate data. Again, we’re filtering out unapproved and spam comments, together with pingbacks.

SELECT CONCAT_WS(', ', MONTHNAME(`comment_date`), YEAR(`comment_date`))
    AS `comment_month`, COUNT(*) as `comment_count`
FROM wp_comments
WHERE `comment_approved` = '1' AND `comment_type` = ''
GROUP BY `comment_month`
ORDER BY `comment_date`;

WordPress MySQL Query

What Time Do People Comment?

Want to know what time you’re getting the most comments? This query will show you the hour and the number of comments published during that hour, so now you know exactly when you should try and be more active on Twitter, eh? Just like with the query above, you can filter out your own comments or perhaps limit the output to the last couple of months.

SELECT HOUR(`comment_date`) AS `comment_hour`,
    COUNT(*) as `comment_count`
FROM wp_comments
WHERE `comment_approved` = '1' AND `comment_type` = ''
GROUP BY `comment_hour`;

WordPress MySQL Query

Most Popular (Top 10) Commenters

You have to know these people since they’re the ones who comment most on your blog, assuming of course you filter out yourself and your blog post authors. You can further filter the query by date to see what the latest trends were and increase the LIMIT if you want to know more.

SELECT `comment_author`, `comment_author_email`,
    COUNT(*) AS `comment_count`
FROM `wp_comments`
WHERE `comment_approved` = '1' AND `comment_type` = ''
GROUP BY `comment_author_email`
ORDER BY `comment_count` DESC
LIMIT 10;

WordPress MySQL Query

That’s it for today friends. Hope you find these SQL queries useful at some point and feel free to share your own snippets using our comment section below. Thanks for stopping by, sharing and staying tuned :)

Konstantin Kovshenin 2 years ago in

Leave a Comment

14 comments