2nd January 2006, 2 min read

My wordpress statistics

Following Mauro, here are my blogging statistics… While we are at it, why not explore a bit how wordpress stores its data?

First, I had to do some cleaning up of the comments to ensure I do not miscount them, let’s remove the spam:
mysql> delete from wp_comments where comment_approved = "spam";
Query OK, 703 rows affected (2.40 sec)


Wow! There was a lot comments marked as spam. Because I marked a lot of them as spam manually, and because I now have a reverse Turing test, I’m not especially interested in the statistics of it all.

Ok, what about some comment statistics already… here’s an interesting query…
select month(comment_date), year(comment_date), count(*) from wp_comments
group by month(comment_date),year(comment_date)
order by year(comment_date),month(comment_date);


Now, what about some posting statistics? When am I most active?

select month(post_date), year(post_date), count(*) from wp_posts group by month(post_date),year(post_date) order by year(post_date),month(post_date);


So, interestingly, there are about as many comments as there are posts and I seem to post, on average, every day.

Then, I started to wonder when people comment on my post? Do they comment the same day or do they comment weeks later? Is the distribution a long tail? Here’s my SQL query…

select round((to_days(post_date)-to_days(comment_date))/10)10 as di,count()
from wp_posts,wp_comments where comment_ID=ID group by di;

And the result is interesting, most comments are made within 90 days, with quite a number of comments made several weeks after I post!

This puts a dent in the theory that blogging is a synchronous conversation. I suspect that most of my comments are made by people who found my blog by accident, not by fervent readers. Maybe because few people read me on a regular basis.