WordPress Core – Optimizing meta_query generated SQL?

by

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?

2 Answers
2

In case I am right about that possible optimisation, I have opened a ticket on Trac and submitted a patch: http://core.trac.wordpress.org/ticket/26281

My plugin’s page query time goes from 10+ seconds to 20ms… I hope there are no edge cases.

Leave a Comment