Make WordPress Core

Opened 9 years ago

Closed 20 months ago

Last modified 19 months ago

#31072 closed defect (bug) (duplicate)

Slow comment-related query slowing down Dashboard

Reported by: archon810's profile archon810 Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.1
Component: Comments Keywords: needs-testing
Focuses: performance Cc:


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:

	COUNT(*) AS num_comments
	comment_type != 'trackback'
AND comment_type != 'pingback'

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 8 years ago.
Add an index to wp_comments comment_type field
31072.1.patch (1.2 KB) - added by leogermani 5 years ago.
Refreshing patch

Download all attachments as: .zip

Change History (16)

#1 @archon810
9 years ago

  • Type changed from enhancement to defect (bug)

#2 @rachelbaker
9 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
8 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:

    comment_approved, COUNT( * ) AS num_comments
    comment_type != 'order_note'

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.


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

#4 @pento
8 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?

8 years ago

Add an index to wp_comments comment_type field

#5 @rodrigosprimo
8 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
8 years ago

  • Keywords comment-type added

#7 @desrosj
5 years ago

  • Keywords needs-refresh added

The most recent patch needs a refresh to apply cleanly.

5 years ago

Refreshing patch

#8 @leogermani
5 years 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
5 years ago

  • Keywords needs-refresh removed

#10 @rodrigosprimo
5 years ago

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

#11 @mukesh27
2 years ago

  • Keywords needs-refresh dev-feedback added

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.

#12 @johnbillion
2 years ago

  • Keywords close needs-testing added; comment-type has-patch needs-refresh dev-feedback removed

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.

#13 @spacedmonkey
20 months ago

  • Resolution set to fixed
  • Status changed from reopened to closed

Fixed as part of [53036].

#14 @desrosj
19 months ago

  • Keywords close removed
  • Milestone Future Release deleted
  • Resolution changed from fixed to duplicate

Duplicate of #19901.

Note: See TracTickets for help on using tickets.