[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):

 See #29560 for an extended set of unit tests that any change in SQL syntax
 has to pass.

 A few thoughts about the patches here:

 1. Core team - this SQL is run through the 'get_meta_sql' filter, and
 changing to subqueries will break many plugins that make funky use of this
 filter. Is there a policy in place about backward compatibility for
 filters on SQL statements? (My take is: if the benefits of changing are
 big enough, make the change, but document it and warn developers.)

 2. Even if we don't switch to subqueries, one conservative step we can
 take, which will address at least part of the problem, is to do better
 initial parsing of the meta_query argument, combining redundant args into
 more efficient chunks. If a plugin dev passes the following meta_query:

 {{{
 'relation' => 'AND',
 array(
     'key' => 'foo',
     'value' => 'bar',
 ),
 array(
     'key' => 'foo',
     'value' => 'barry',
 ),
 }}}

 we can safely translate it to the following and save a table join:

 {{{
 array(
     'key' => 'foo',
     'value' => array( 'bar', 'barry', )
     'compare' => 'IN',
 ),
 }}}

 3. More detailed benchmarks about the switch to subqueries would make the
 case for a switch much more persuasive. The post linked to by ve9gra in
 https://core.trac.wordpress.org/ticket/24093#comment:19 is a good start,
 but ideally we'd have a script for generating test data (maybe a wp-cli
 subcommand) for the core team to test with, and/or more detailed specs
 about server environment, the matrix of queries tested, etc.

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