mysql – Control panel loads slowly due to 2 slow queries on comment table
Our WP site’s control panel loads very slowly. Analyzing this with the Query Monitor plugin, I see that it is slowed down by two queries, both on the comment table.
SELECT comment_approved, COUNT( * ) AS total FROM wp_comments GROUP BY comment_approved
Takes about 0.4 seconds (pretty fixed).
SELECT COUNT(*) FROM wp_comments WHERE ( ( comment_approved = '0' OR comment_approved = '1' ) ) AND user_id = <my-user-id> ORDER BY wp_comments.comment_date_gmt DESC
Takes about 1.5 seconds, but can take much more (saw it taking 7-8 seconds once).
Site’s comment table contains about 1 million entries. Add to that the fact that we have about 20 editors, few of which logged in at the same time, and this could explain these numbers pretty much.
Now, googling this issue, found that the first query is probably the result of showing the comment number in the control panel. I tried to disable this view, but still see this query.
But the more worrying one is the second one: I can’t find who runs it. Googling it, found that it may be executed by Akismet plugin (which we have), but disabling the plugin doesn’t make any difference.
So, how can I find who runs this query? If it’s a well known issue, how do I fix it? I guess I can add two indexes to the comment table, but this will have its toll on updating the table (especially given the large number of entries). I simply want first to find the culprit, then decide how to handle it.