1

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?

1

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.

-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.

4
  • 1
    Can you please explain how what you're suggesting solves the problem.
    – Nicolai
    Nov 27 '13 at 20:12
  • Piklist makes adjustments to the query for you to make sure it executes quickly regardless of how many meta queries are made and how many posts are in your system. Nov 27 '13 at 21:18
  • 1
    So it really does what you're claiming...just teasing you. But of course I meant in terms of code or at least explanation of functionality - in detail, not in general -, this way it looks a bit like self-promotion; just saying.
    – Nicolai
    Nov 27 '13 at 21:57
  • Hi, could you at least point me to the proper place in your code where you implement your workaround? I'd like that to be independent of any plugin. Dec 3 '13 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.