Slow comment-related query slowing down Dashboard

[archon810]

Not sure if it's related to #22301, but I sniped a query running when the Dashboard is running on a busy site with 810k entries in the wp_comments table.

The unoptimized query is as follows:

SELECT
	comment_approved,
	COUNT(*) AS num_comments
FROM
	wp_comments
WHERE
	comment_type != 'trackback'
AND comment_type != 'pingback'
GROUP BY
	comment_approved

The query doesn't use an index and ends up in the nasty Using where; Using temporary; Using filesort. This query runs in 3+ seconds for me.

The following index resolves the issue, though the query is still relatively slow (0.6s, but this timing is much more consistent across runs, even with SQL_NO_CACHE): (comment_approved, comment_type). Now the query uses a much less nasty Using where; Using index.

[rachelbaker]

@archon810 Thank you for reporting this issue. This does look to be a duplicate of #22301 to me, so I am going to close this ticket.

[rodrigosprimo]

I'm reopening this ticket after talking to @rachelbaker as I don't think it is a duplicate of #22301.

I'm facing a very similar issue with a slightly different query that is significantly improved if a index is added to comment_type column.

Plugins like WooCommerce or Easy Digital Downloads use custom comment types to store different kinds of data. They then use the wp_count_comments filter to exclude custom comment types from comments count. Using WooCommerce as an example, the resulting query to count comments is:

SELECT
    comment_approved, COUNT( * ) AS num_comments
FROM
    wp_comments
WHERE
    comment_type != 'order_note'
GROUP BY
    comment_approved;

This query runs for every page that displays the admin top bar and, in one of my sites which has about 70k comments, it takes +5 seconds. Adding the index below makes the query run in 1.7 seconds, which is still slow but much better.

ALTER TABLE `wp_comments` ADD INDEX `idx_comment_type` (`comment_type`);

I'm not well versed in database performance improvements so I'm not sure what are the implications of this change and if there is another approach to solve this problem.

Thoughts?

[pento]

Thanks for the extra information, @rodrigosprimo!

This seems like a fairly simple use case, and one that's certainly likely to become more common once a full custom comment type API is added.

We should consider an index like this, when that happens. In the mean time, I don't mind if plugins add their own index to the table, but it'd be nice if you did the following:

• Prefix the index name with woo_, so that it will never clash with a Core index name.
• Keep subscribing to this ticket, so you know when a Core index has landed - you can then update your code to stop adding the custom index, and to remove the redundant index from exisiting installs (once they've updated their Core version, that is).

@rachelbaker: I don't suppose you have a Trac keyword you're using to track custom comment type tickets?

[rodrigosprimo]

Add an index to wp_comments comment_type field

[rodrigosprimo]

The patch above is my first patch changing WordPress database structure so I'm probably missing some parts. I was specially unsure about which db_version number to use when checking if the code to add the new index should run or not. I used the last SVN revision.

I noticed that previous indexes that were created are first dropping the index so I followed the same pattern. Is this to make sure the query doesn't generate an error in case the index already exist in the database?

[desrosj]

The most recent patch needs a refresh to apply cleanly.

[leogermani]

Refreshing patch

[leogermani]

Met @rodrigosprimo here at the DevDay in WordCamp Europe 2019 and gave his patch a refresh :)

[rodrigosprimo]

@desrosj please let me know if there is anything else I can do to help move this ticket forward. Thanks.

[mukesh27]

Hi there!

The patch needs a refresh to apply cleanly.

@desrosj Is this ticket on your to-do list for the upcoming WP update?

dev-feedback added so other code dev check it and possible that we get some movement on this.

[johnbillion]

I believe this was fixed in #19901 (note the SQL has changed since the original report). Needs confirming, and if so this ticket can be closed.

[spacedmonkey]

Fixed as part of [53036].

[desrosj]

Duplicate of #19901.