Make WordPress Core

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's profile archon810 Owned by: boonebgorges's profile 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 @wonderboymusic
9 years ago

  • Keywords needs-patch added
  • Milestone changed from Awaiting Review to Future Release

#3 @wonderboymusic
9 years ago

  • Milestone changed from Future Release to 4.4
  • Owner set to boonebgorges
  • Status changed from new to assigned

#4 follow-up: @boonebgorges
9 years ago

archon810 - Can you tell me where this query is coming from? Is this a query you see when viewing post 87814 on the frontend?

#5 in reply to: ↑ 4 @archon810
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.

#6 @DrewAPicture
9 years ago

@boonebgorges How would you like to proceed here for 4.4? Seems like nailing down the source of the query is still up in the air.

#7 @boonebgorges
9 years ago

  • Milestone changed from 4.4 to Future Release

#8 @pbearne
8 months ago

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

looking at the code /schema.php:65


        KEY comment_date_gmt (comment_date_gmt),

it looks like the is now set so I will close thick as fixed

Note: See TracTickets for help on using tickets.