WordPress.org

Make WordPress Core

Opened 8 years ago

Closed 5 years ago

#4366 closed enhancement (fixed)

Comment table could use some more indices

Reported by: markjaquith Owned by: markjaquith
Milestone: 2.9 Priority: normal
Severity: normal Version: 2.9
Component: Performance Keywords: has-patch
Focuses: Cc:

Description

Comments have a scaling issue related to the "slow down, cowboy" IP-based throttling SELECT query. We need to use an index here to optimize the query.

Attachments (1)

4366.diff (1.0 KB) - added by mdawaffe 5 years ago.

Download all attachments as: .zip

Change History (13)

comment:1 @rob1n8 years ago

  • Summary changed from Comment table could use some more indexes to Comment table could use some more indicies

comment:2 @JeremyVisser8 years ago

  • Summary changed from Comment table could use some more indicies to Comment table could use some more indices

comment:3 @ffemtcj7 years ago

  • Milestone changed from 2.5 to 2.6

comment:4 follow-up: @mrmist6 years ago

  • Keywords needs-patch added

If this is still desired, then as the query stands -

SELECT comment_date_gmt FROM wp_comments WHERE comment_author_IP = '10.1.1.1' OR comment_author_email = 'test' ORDER BY comment_date DESC LIMIT 1;

You'd need an index

create index comment_auth ON wp_comments (comment_author_ip, comment_author_email, comment_date_gmt, comment_date)

Anything less and mysql won't bother with it (though you may see different results depending on your test data I suppose).

However, I'm not sure why the query orders on comment_date and fetches comment_date_gmt? (That seems bugish). Unless there is some specific reason, I'd suggest changing the original query so that it orders on comment_date_gmt, as then you could make the covering index for this query smaller -

create index comment_auth ON wp_comments (comment_author_ip, comment_author_email, comment_date_gmt)

Sorry I have no idea how you guys write the patches that deal with changing the database.

comment:5 @Denis-de-Bernardy6 years ago

  • Component changed from Administration to Performance
  • Owner changed from anonymous to Denis-de-Bernardy
  • Status changed from new to accepted

comment:6 @Denis-de-Bernardy6 years ago

  • Owner Denis-de-Bernardy deleted
  • Status changed from accepted to assigned

comment:7 in reply to: ↑ 4 @hakre5 years ago

Replying to mrmist:

Sorry I have no idea how you guys write the patches that deal with changing the database.

please see wp-admin/includes/upgrade.php

comment:8 @westi5 years ago

  • Milestone changed from 2.9 to Future Release

No patch available.

Moving to Future Release for now.

comment:9 @mdawaffe5 years ago

We already have a good index for this: comment_date_gmt. Attached is a patch that limits the query to looking for matches within the last 1 hour (arbitrary time period).

@mdawaffe5 years ago

comment:10 @mdawaffe5 years ago

  • Keywords has-patch added; needs-patch removed

comment:11 @markjaquith5 years ago

  • Milestone changed from Future Release to 2.9
  • Owner set to markjaquith
  • Status changed from assigned to accepted
  • Version set to 2.9

comment:12 @markjaquith5 years ago

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

(In [12376]) Optimize Whoa Cowboy query. Only look back one hour. props mdawaffe. fixes #4366

Note: See TracTickets for help on using tickets.