#31072 closed defect (bug) (duplicate)
Slow comment-related query slowing down Dashboard
Reported by: |
|
Owned by: | |
---|---|---|---|
Milestone: | Priority: | normal | |
Severity: | normal | Version: | 4.1 |
Component: | Comments | Keywords: | needs-testing |
Focuses: | performance | Cc: |
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)
Change History (16)
#2
@
10 years ago
- Milestone Awaiting Review deleted
- Resolution set to duplicate
- Status changed from new to closed
#3
@
9 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?
#4
@
9 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?
#5
@
9 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?
#7
@
6 years ago
- Keywords needs-refresh added
The most recent patch needs a refresh to apply cleanly.
#8
@
6 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 :)
#10
@
6 years ago
@desrosj please let me know if there is anything else I can do to help move this ticket forward. Thanks.
#11
@
3 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
@
3 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.
@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.