
In a blog entry on http://blogs.arno.fi/isit/2009/05/14/home-made-blog-statistics-from-wordpress-mu/ I describe what I did.
The blog entry may be of interest for those who use WordPress and are set back by the huge amounts of tables it generates. I happen to host 18 blogs and with each blog requiring 8 tables, that's a total of 144 tables. Add the 9 top-level blogs and I've got 153 tables to navigate.
The blog entry I wroteÂÂ
- identifies the key fields
- shows how to do stats on individual blogs
- creates a statistics table into which I aggregate relevant entries from individual blog tables
- uses SELECT to generate SELECT statements
- and ends up with some statistics on the 253 blog entries in my WordPress.


wp_n_comments
tables, and came to the conclusion that I'll need to use some global DELETEs to clean up spam comments that have found their way to my site.I found out that, strangely enough, I haven't approved a single comment that includes the character string "viagra"
select comment_approved,count(*)
from wp_4_comments
where comment_content like "%viagra%"
group by comment_approved;
The same applies to "cialis".
So I issued
delete from wp_4_commentsÂÂ
where comment_content like "%cialis%";delete from wp_4_comments
where comment_content like "%viagra%";
but I still have quite a bit of cleaning up to do, since despite deleting first 759 cialis entries and then 411 viagra entries, I still have 3683 unapproved comments to clean up (and I suspect there are less than 10 real comments that have slipped my attention in my inbox, when I've got notification of them).
At any rate, I got my statistics and had more efficient and fun (albeit incomplete) spam cleaning than ever before!