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 2.5 million comments (most of them are of custom WooCommerce comment type called 'order_note'), it takes +5 seconds. Adding the index below makes the query run in 0.3 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.