Optimizing meta_query generated SQL? -> 9s to 20ms
|Reported by:||vprat||Owned 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?