WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 4 years ago

#16826 closed enhancement (invalid)

Optimize SQL for term exclusions

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

Description

When calling WP_Query with an argument like 'cat=-1', the WP_Tax_Query::get_sql() method uses a dependant subquery to retrieve posts that don't belong to that category.

This can be achieved in a more efficient way by using a LEFT JOIN.

Attachments (1)

16826.diff (806 bytes) - added by scribu 4 years ago.

Download all attachments as: .zip

Change History (7)

@scribu4 years ago

comment:1 @scribu4 years ago

  • Component changed from Taxonomy to Performance
  • Keywords 3.2-early added
  • Milestone changed from Awaiting Review to Future Release

Related: #16706

comment:2 @scribu4 years ago

Also fixes #16812 apparently.

comment:3 @scribu4 years ago

  • Keywords 3.2-early removed
  • Milestone changed from Future Release to 3.2
  • Owner set to scribu
  • Status changed from new to accepted

comment:4 @ocean904 years ago

Related: #16967

comment:5 @ryan4 years ago

Won't that incur "Using temporary"?

comment:6 @scribu4 years ago

  • Milestone 3.2 deleted
  • Resolution set to invalid
  • Status changed from accepted to closed

Indeed.

Testing again gives me 0.0955 seconds for the original query and 1.0281 seconds for the LEFT JOIN. 2 order of magnitudes slower.

I guess I was confused by the DEPEDANT SUBQUERY wording. It's not dependant at all; it' a unique subquery, which is pretty fast.

Note: See TracTickets for help on using tickets.