Opened 11 years ago
Closed 11 years ago
#26281 closed enhancement (duplicate)
Optimizing meta_query generated SQL? -> 9s to 20ms
Reported by: | vprat | Owned by: | |
---|---|---|---|
Milestone: | Priority: | normal | |
Severity: | major | Version: | 3.8 |
Component: | Query | Keywords: | has-patch |
Focuses: | Cc: |
Description
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?
Attachments (1)
Change History (6)
#1
@
11 years ago
I don't know if there are automated tests that already exist for that class, but I'd love to know if my patch is causing something to break. We may be able to isolate such obvious cases to optimize most queries and let the other queries be processed with all required JOINs.
#2
follow-up:
↓ 4
@
11 years ago
- Component changed from General to Query
Hi vprat. Thanks very much for the bug report and patch. I think this overlaps with another ticket, but I can't locate it at the moment. Let me see if someone else can. If we can combine the research and work in both, we should be able to come up with a well-tested candidate for core.
There are some nascent tests for that class here:
https://core.trac.wordpress.org/browser/trunk/tests/phpunit/tests/meta/query.php
#3
follow-up:
↓ 5
@
11 years ago
Apparently, the optimisation would not be valid for AND relation. However, this is perfectly right for OR relation.
So I guess just writing a special case for the OR relationship (in which case just use a single JOIN) would do the trick.
The ticket you are looking for is https://core.trac.wordpress.org/ticket/24093 and has a patch too (probably a better one).
#4
in reply to:
↑ 2
@
11 years ago
Replying to nacin:
I think this overlaps with another ticket, but I can't locate it at the moment.
Apparently, the optimisation would not be valid for AND relation. However, this is perfectly right for OR relation. So I guess just writing a special case for the OR relationship (in which case just use a single JOIN) would do the trick.
The ticket you are looking for is https://core.trac.wordpress.org/ticket/24093 and has a patch too (probably a better one).
#5
in reply to:
↑ 3
@
11 years ago
- Milestone Awaiting Review deleted
- Resolution set to duplicate
- Status changed from new to closed
Replying to vprat:
Apparently, the optimisation would not be valid for AND relation. However, this is perfectly right for OR relation.
So I guess just writing a special case for the OR relationship (in which case just use a single JOIN) would do the trick.
Indeed, on all counts! Feel free to jump in on #24093 if you can help.
Patch to avoid multiple JOINs for the meta query