Opened 10 years ago
Closed 8 months ago
#31188 closed defect (bug) (fixed)
Very slow query in comments_template -> get_comments -> WP_Comment_Query->query
Reported by: | archon810 | Owned by: | boonebgorges |
---|---|---|---|
Milestone: | Priority: | normal | |
Severity: | normal | Version: | 4.1 |
Component: | Comments | Keywords: | needs-patch |
Focuses: | performance | Cc: |
Description
In my quest to improve performance of core Wordpress functionality (see #31071, #31072, and #31171), I'm back with another optimization that is quite significant for large WP installations with lots of comments on some posts.
The query in question:
# Query_time: 25.314234 Lock_time: 0.000074 Rows_sent: 10 Rows_examined: 699220 SET timestamp=1422666393; SELECT wp_comments.* FROM wp_comments WHERE comment_post_ID = '87814' AND comment_approved = '1' ORDER BY comment_date_gmt DESC LIMIT 10;
As you can see, 25s is not great to say the least. The query isn't using an optimized index, which I've now created, at which point it started running in milliseconds.
The index is as follows:
CREATE INDEX `comment_post_ID_approved_date_gmt` ON `wp_comments`(`comment_post_ID`, `comment_approved`, `comment_date_gmt`);
I've verified the performance improvements using SQL_NO_CACHE as well as EXPLAIN.
The post in question here had over 3000 comments, which isn't uncommon on our site (androidpolice.com).
Interestingly, MySQL queries using a different strategy internally which isn't nearly this slow for posts with fewer comments. But at some point, it switches it up because it thinks it's better, and things go sour.
Change History (8)
This ticket was mentioned in Slack in #core by ocean90. View the logs.
10 years ago
#2
@
9 years ago
- Keywords needs-patch added
- Milestone changed from Awaiting Review to Future Release
#3
@
9 years ago
- Milestone changed from Future Release to 4.4
- Owner set to boonebgorges
- Status changed from new to assigned
#5
in reply to:
↑ 4
@
9 years ago
Replying to boonebgorges:
archon810 - Can you tell me where this query is coming from? Is this a query you see when viewing post 87814 on the frontend?
I can't be 100% sure anymore, but looking at the query, it just looks like the post page comments.
archon810 - Can you tell me where this query is coming from? Is this a query you see when viewing post 87814 on the frontend?