[wp-trac] [WordPress Trac] #26281: Optimizing meta_query generated SQL? -> 9s to 20ms

[WordPress Trac]

#26281: Optimizing meta_query generated SQL? -> 9s to 20ms
-------------------------+-----------------------------
 Reporter:  vprat        |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  General      |    Version:  trunk
 Severity:  major        |   Keywords:  has-patch
-------------------------+-----------------------------
 I have a problem in a plugin of mine which uses post meta to store
 ownership of a post. Basically, my issue is that the WP_Meta_Query class
 generates one JOIN per meta_query query. I don't understand why we cannot
 us the same JOIN for all queries.

 Of course, when using more than 5 or 6 queries, the query takes way too
 much time to complete and plugin fails to get the posts.

 E.g.: this is what I would do to get the posts that belong to either users
 5, 6, 8 and 10 (In the real plugin, the compare is always LIKE and value
 looks like '%|usr_5|%' to solve some cases for advanced ownership
 control):

 {{{
 'meta_query' => array(
   'relation' => 'OR',
   array(
     'key'      => 'owner',
     'value'    => 5,
     'compare'  => '='
   ),
   array(
     'key'      => 'owner',
     'value'    => 6,
     'compare'  => '='
   ),
   array(
     'key'      => 'owner',
     'value'    => 8,
     'compare'  => '='
   ),
   array(
     'key'      => 'owner',
     'value'    => 10,
     'compare'  => '='
   )
 )
 }}}

 That above generates a SQL query that has 4 JOIN statements (mt1 to mt4)
 and in the WHERE clause, is using each JOIN for a comparison. Something
 like:

 {{{
 mt1.meta_key = 5 OR mt2.meta_key = 6 OR mt3.meta_key = 8 OR mt4.meta_key =
 10
 }}}

 What is the point of that??! All of this could be done using a single JOIN
 and a WHERE clause like:

 {{{
 mt1.meta_key = 5 OR mt1.meta_key = 6 OR mt1.meta_key = 8 OR mt1.meta_key =
 10
 }}}

 Is there something I am missing? Is that multiple JOIN there to take care
 of some comparison types?

--
Ticket URL: <http://core.trac.wordpress.org/ticket/26281>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software