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.
I also studied the
wp_n_commentstables, 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"
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!