Opened 2 years ago

Closed 2 years ago

#16826 closed enhancement (invalid)

Optimize SQL for term exclusions

Reported by: scribu Owned by: scribu
Priority: normal Milestone:
Component: Performance Version:
Severity: normal Keywords: has-patch
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 2 years ago.

Download all attachments as: .zip

Change History (7)

scribu — 2 years ago

comment:1   scribu — 2 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   scribu — 2 years ago

Also fixes #16812 apparently.

comment:3   scribu — 2 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   ocean90 — 2 years ago

Related: #16967

comment:5   ryan — 2 years ago

Won't that incur "Using temporary"?

comment:6   scribu — 2 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.