[wp-trac] [WordPress Trac] #38173: Meta query creates unecessary multiple left joins when using the same meta key

[WordPress Trac]

#38173: Meta query creates unecessary multiple left joins when using the same meta
key
-------------------------+-----------------------------
 Reporter:  neonWired    |       Owner:
     Type:  enhancement  |      Status:  new
 Priority:  normal       |   Milestone:  Future Release
Component:  Query        |     Version:  3.2
 Severity:  normal       |  Resolution:
 Keywords:  needs-patch  |     Focuses:  performance
-------------------------+-----------------------------
Changes (by boonebgorges):

 * keywords:   => needs-patch
 * type:  defect (bug) => enhancement
 * version:  4.6 => 3.2
 * component:  Database => Query
 * milestone:  Awaiting Review => Future Release


Comment:

 > Bug was registered 6 months ago and no one care about this?

 There are many open enhancement requests in WordPress, and many of them
 are worthwhile, but there are only so many people reviewing and writing
 patches.

 @neonWired is correct that no table join is required for subclauses that
 are joined by `OR` that both operate on the same `meta_key` when one of
 them uses the compare operator `NOT EXISTS`. But this syntax requires that
 the `meta_key` comparison be put into the `ON` clause for that particular
 `JOIN` clause. This already happens for `NOT EXISTS`:
 https://core.trac.wordpress.org/browser/tags/4.7.2/src/wp-includes/class-
 wp-meta-query.php#L536 But the logic becomes a good deal more complicated
 when other clauses share a `JOIN` with a `NOT EXISTS` clause, because of
 the order in which `WP_Meta_Query` currently builds SQL. Take @neonWired's
 query as an example. Here's what happens internally:

 1. SQL is generated for the first subclause (`'compare' => '!='`). The
 JOIN clause looks like `INNER JOIN wp_postmeta ON ( wp_posts.ID =
 wp_postmeta.post_id )`
 2. SQL is generated for the second subclause after this. `WP_Meta_Query`
 realizes it's a 'NOT EXISTS', so it knows that the JOIN clause must be
 like `LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id AND
 wp_postmeta.meta_key = 'product' )`
 3. But in order to share the table join with the clause generated in 1,
 we'd have to go back and modify the SQL that's already generated there, so
 that it has the proper JOIN syntax.

 SQL generation for clauses is more or less linear at the moment; there's
 no easy way to jump back and change the syntax of a clause that already
 been generated. We'd need to either (a) change the way that the clauses
 are generated so that it's easier to change existing clauses, or (b)
 create some sort of look-ahead device that can tell whether there's going
 to be a 'NOT EXISTS' clause involved, and if so, process it first.

 Either of these is quite possible, but it's not a trivial task. I've just
 spent an hour or so trying to come up with a proof-of-concept, but I
 haven't been successful. If anyone would like to have a go at writing a
 patch, I'd be happy to help to review it.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/38173#comment:3>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform