WordPress Core – Optimizing meta_query generated SQL?

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.

Read More

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?

Related posts

2 comments

  1. We ran into this issue while developing Piklist. That being said we put a lot of time and testing into a solution that works without breaking your site or other plugins. To solve your problem just download and install Piklist and then try your query again. Now Piklist does come with quite a bit (its a framework) but it doesn’t add much overhead to the system.

    NOTE: The current version is in beta but there are no known bugs affecting WordPress in the current release, just some bugs within the core we are working out before the production launch in December.

    http://downloads.wordpress.org/plugin/piklist.zip

    Let me know if you have any questions.

Comments are closed.