mysql - Control panel loads slowly due to 2 slow queries on comment table

admin管理员组

文章数量:1287497

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.

First one:

SELECT comment_approved, COUNT( * ) AS total
FROM wp_comments
GROUP BY comment_approved

Takes about 0.4 seconds (pretty fixed).

Second one:

SELECT COUNT(*)
FROM wp_comments
WHERE ( ( comment_approved = '0'
OR comment_approved = '1' ) )
AND user_id = <my-user-id>
ORDER BY wp_commentsment_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.

本文标签： mysqlControl panel loads slowly due to 2 slow queries on comment table