[wp-trac] [WordPress Trac] #24093: WP_Meta_Query is inefficient when referencing the same keys in "OR" query

[WordPress Trac]

#24093: WP_Meta_Query is inefficient when referencing the same keys in "OR" query
-------------------------------------------------+-------------------------
 Reporter:  sc0ttkclark                          |       Owner:
     Type:  defect (bug)                         |      Status:  new
 Priority:  normal                               |   Milestone:  Future
Component:  Query                                |  Release
 Severity:  normal                               |     Version:  3.5.1
 Keywords:  needs-refresh meta-query needs-      |  Resolution:
  unit-tests has-patch dev-feedback              |     Focuses:
                                                 |  performance
-------------------------------------------------+-------------------------

Comment (by boonebgorges):

 I want to continue looking into the possibility of subqueries. But, for
 the time being, the idea behind sc0ttclark's initial
 [attachment:24093.patch] could, I think, be implemented right away. The
 idea has become more complicated now that the logic of WP_Meta_Query is
 recursive [29887] - using a single JOIN for multiple clauses only works if
 'relation=OR', but the recursive logic means that there could be any
 number of relations in a query tree. I think it can look something like
 this:

 1. In get_sql_for_clause(), tirst time a meta_key is referenced, create a
 new alias and JOIN clause.
 2. Store the alias in an array keyed by the meta_key (so far, it's like in
 your patch)
 3. The next time you see a clause that has a meta_key that already has an
 alias, check to see whether the $parent_query has OR as a relation. If so,
 use the existing alias.

 This will break down when you get several generations deep, though. If you
 have a query like this, I don't think you can combine the 'foo' and 'foo1'
 tables in this kind of query:

 {{{
 array(
     'relation' => 'AND',
     array(
         'relation' => 'OR',
         array(
             'key' => 'foo',
             'value' => 'bar',
         ),
         array(
             'key' => 'foo1',
             'value' => 'bar',
         ),
     ),
     array(
         'relation' => 'OR',
         array(
             'key' => 'foo',
             'value' => 'baz',
         ),
         array(
             'key' => 'foo1',
             'value' => 'baz',
         ),
     )
 )
 }}}

 Clearly, this will need some thought, and some unit tests, and maybe some
 compromises regarding how many JOINS we can eliminate in case of multiply-
 nested queries. Who is up for a Really Fun Time writing this patch?

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