| I think it may be this "widget" (I don't know the WP ecosystem, but this seems reasonable):
http://wordpress.org/extend/plugins/recent-comments-widget-w... I only see one query in there:
$comments = $wpdb->get_results("SELECT $wpdb->comments.* FROM $wpdb->comments JOIN $wpdb->posts ON $wpdb->posts.ID = $wpdb->comments.comment_post_ID WHERE comment_approved = '1' AND post_status = 'publish' ORDER BY comment_date_gmt DESC LIMIT 150"); I'm not sure why they are fetching 150 (why would you use that many?) but this query could be slow if you have many posts and many comments. Also, the goofy thing is that they are wiping out the cache every time and regenerating it instead of (say) incrementally adding to it. I'd re-write it with an exists, which may help performance instead of a join (I've seen MySQL be pretty dumb with joins when you basically want an exists): SELECT whatever
FROM comments
WHERE EXISTS( SELECT * FROM posts WHERE ... )
AND comment_approved = 1
ORDER BY
LIMIT xx In this case, MySQL may be having a problem where it can't read a multi-column index on post_id and comment_date_gmt DESC (I'm not sure if you can specify ordering in MySQL indexes, been a while), thus it's basically doing an in-memory sort of every approved comment (on a published post, which is likely all of them) in the system, which in his case was about ~200K. This could definitely take several seconds if there isn't enough memory to do it all at once (lots of swapping). Personally, I'd try and modify the logic to be more conducive to indexing - do you really care if a comment was on a post that you made 10 years ago? MySQL also (IIRC) doesn't have bitmap indexes, so that "comment_approved" thing may or may not invalidate an index usage unless you add it to the index. I'd probably do something like: the "n" (where n is < 150) most recent comments to a post made within the last n number of days, so you can really cut down on the number of rows you need to sort. |