WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#9720 closed defect (bug) (fixed)

Why the left joins in wp-include/query.php?

Reported by: Denis-de-Bernardy Owned by:
Milestone: 2.8 Priority: normal
Severity: normal Version: 2.8
Component: Optimization Keywords: has-patch tested commit
Focuses: Cc:

Description

While looking into #9635, I noticed a whole bunch of left joins in the main queries, where a straightforward join would usually seem perfectly valid.

Are there any reasons for them to be used? Asking, because imporperly used left joins can have devastating effects on a query's performance by forcing the join order.

Attachments (1)

9720.diff (1.9 KB) - added by Denis-de-Bernardy 5 years ago.
refreshed against 11255

Download all attachments as: .zip

Change History (5)

comment:1 Denis-de-Bernardy5 years ago

  • Keywords has-patch needs-testing added

Example:

SELECT foo.*
FROM foo
JOIN bar USING ( id )
WHERE bar.col = 'val'

-> uses the index on bar(col) // fast

SELECT foo.*
FROM foo
LEFT JOIN bar USING ( id )
WHERE bar.col = 'val'

-> depending on how the query rewriter optimizes this (it could be rewritten as above), either the above plan or:
hash join on shared key, filter on bar.col = 'val' // amounts to a seq scan in our case, so very slow

Denis-de-Bernardy5 years ago

refreshed against 11255

comment:2 Denis-de-Bernardy5 years ago

  • Keywords tested commit added; needs-testing removed

comment:3 ryan5 years ago

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

(In [11259]) JOIN instead of LEFT JOIN. Props Denis-de-Bernardy. fixes #9720

comment:4 ryan5 years ago

(In [11452]) Restore LEFT JOIN for post_status_join. fixes #9851 see #9720

Note: See TracTickets for help on using tickets.