WordPress.org

Make WordPress Core

Opened 5 years ago

Last modified 4 months ago

#31072 reopened defect (bug)

Slow comment-related query slowing down Dashboard

Reported by: archon810 Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version: 4.1
Component: Comments Keywords: comment-type has-patch
Focuses: performance Cc:
PR Number:

Description

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.

Attachments (2)

31072.patch (1.2 KB) - added by rodrigosprimo 3 years ago.
Add an index to wp_comments comment_type field
31072.1.patch (1.2 KB) - added by leogermani 4 months ago.
Refreshing patch

Download all attachments as: .zip

Change History (12)

#1 @archon810
5 years ago

  • Type changed from enhancement to defect (bug)

#2 @rachelbaker
4 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

@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.

#3 @rodrigosprimo
3 years ago

  • Resolution duplicate deleted
  • Status changed from closed to reopened

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?

Last edited 3 years ago by rodrigosprimo (previous) (diff)

#4 @pento
3 years ago

  • Keywords needs-patch added
  • Milestone set to Future Release

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
3 years ago

Add an index to wp_comments comment_type field

#5 @rodrigosprimo
3 years ago

  • Keywords has-patch added; needs-patch removed

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?

#6 @rachelbaker
3 years ago

  • Keywords comment-type added

#7 @desrosj
7 months ago

  • Keywords needs-refresh added

The most recent patch needs a refresh to apply cleanly.

@leogermani
4 months ago

Refreshing patch

#8 @leogermani
4 months ago

  • Keywords changed from has-patch comment-type needs-refresh to comment-type needs-refresh has-patch

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

#9 @leogermani
4 months ago

  • Keywords needs-refresh removed

#10 @rodrigosprimo
4 months ago

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

Note: See TracTickets for help on using tickets.