I believe this fix didn't take things far enough. For example, a meta_query that does a lot of comparing on the same meta_key, will have a JOIN and a WHERE for each query array. It shouldn't have to re-join the postmeta table on each query array though, since they are based on the same meta_key.
I'll attach a suggested patch that will only join if it needs to, and use the previous alias for the first meta_key joined table if it's already been joined.
24093.patch​ (1.8 KB) - added by sc0ttkclark 6 years ago.
24093.1.patch​ (1.0 KB) - added by kevinfodness 5 years ago.
24093.2.patch​ (2.2 KB) - added by vprat 5 years ago.
24093.3.patch​ (2.9 KB) - added by vprat 5 years ago.
24093.4.patch​ (2.5 KB) - added by vprat 5 years ago.
24093.5.patch​ (23.2 KB) - added by boonebgorges 5 years ago.
Stumbled upon this patch/ticket while updating Codex on ​WP_Meta_Query. Nice patch! Finally.
Thanks for the feedback, I'll have a look and update the patch accordingly.
@sc0ttkclark Please keep in mind that the $alias will be used in all returning WHERE clauses, so that needs consideration as well.
For sure, will look this patch over again and see what it'll take to bring it to it's original intention.
#26281 was marked as a duplicate.
I wrote a patch that uses subqueries instead of JOIN operations and seems to be significantly more efficient. It should work fine for both OR and AND operations. I tested the patch on a relatively large site running WordPress 3.8.
I'd be interested to get the opinions of core contributors and see the results of unit tests to determine if this is a workable solution.
The previous patch I submitted was git formatted - per guidelines I converted it to an SVN formatted patch and attached it to this issue.
any update about that issue or plans to incorporate the fix in core for 3.8.x?
I installed the patch and for me it works very good. I hope this update will be incorporated into the next WP release.
kevinfodness Your modification works fantastic when You need to query thru multiply meta_keys but it breaks order_by meta_key statment.
I need badly this fix so I have given it another look. Starting from the second patch, I have managed to fix the DB errors that have been mentionned by Mr.Defi.
I have managed to fix the orderby meta issue.
In the WP_Query class, for ordering, we simply use the mt0 alias instead of the main meta table name.
The rest is pretty similar to what had been proposed by the original patchers.
Let me know if there is any test case that fails and how to reproduce it, I'll try to fix the issue. I would love that patch to make its way into WP 4.0.
This "bug" reared its ugly head after using a plugin by vprat. Using mySQL profiling I found the impact to be crippling after adding more than a few groups to a user. And keep in mind that this install is very small... like around 15 posts. I'm sure a site with more posts would get impacted much earlier as adding multiple INNER JOIN statements blow up the tempDB exponentially.
I would really like to have this merged into core as soon as possible. I've tested the patch on 3.9.1 with no issues - or at least none that I could find.
Sounds like we still need patch review, unit tests, and profiling data.
Come on, that ticket is more than a year old and is derived from a 3 year old ticket that had an incomplete fix.
Profiling data is pretty simple. A query with 6 meta query elements will simply never complete without the patch. When having 4 meta query elements, as said, it goes from a few minutes to a few milliseconds.
Unit tests have been provided with the ticket referenced in the ticket of origin. I guess they are still valid.
I would love if somebody could review the patch here (not my wheelhouse), and if we could get unit tests into the same patch for overall review. We are still too late for 4.0, though.
It would be good to get this finally in. I happened to stuble on this when I was looking for an answer to a problem I have with a dynamically generated meta_query. When the amount of the meta_query arrays exceeds nine my local development server and my staging server both just freeze. The DB query seems to run indefinitely and I need to restart the MySQL server to get the site operational again.
EDIT 1: I can confirm that vprat's latest patch fixes the issue on my development and test server.
EDIT 2: The patch doesn't seem to break anything but cases WP to show errors on the admin side, example below.
There's no mention of a limit in the WP Meta Query class reference. The meta_query implementation creates so bad SQL that it actually can cripple the whole server with only 9 compares. I think the type of this should be changed to bug.
In general I also feel that it's weird that the meta_query implementation differs so much from the taxonomy_query implementation (f.ex. missing the support for multi level query with different compares).
See #29560 for an extended set of unit tests that any change in SQL syntax has to pass.
More detailed benchmarks about the switch to subqueries would make the case for a switch much more persuasive. The post linked to by ve9gra in https://core.trac.wordpress.org/ticket/24093#comment:19 is a good start, but ideally we'd have a script for generating test data (maybe a wp-cli subcommand) for the core team to test with, and/or more detailed specs about server environment, the matrix of queries tested, etc.
@sarukuku - "The patch doesn't seem to break anything but cases WP to show errors on the admin side"
Can you please specify some steps to reproduce this? I would be happy to fix it.
It is always somehow risky for a plugin author to play with SQL directly instead of using the WP core functions. So I feel these cases should be adressed by plugin authors (and this is why I submitted a patch to improve WP Core instead of patching my plugin with such a filter).
This is very easy to reproduce. You simply need to issue a WP_Query with at least 5 clauses in the meta query. Even if you only have a few posts (10/20) in the database it will blow (will not even complete). With the patch, the query takes a few milliseconds in all cases.
@vprat - It seems to be only the WPSearch plugin (​https://searchwp.com/) that is confused about the changes in the patch on the environment I tested the it. Plain WP installation doesn't seem to have any problems with the patch as far as I can see.
Patch works, random plugin was confused and caused the error.
The unit tests in #29560 are great for this ticket - they're showing a bunch of bugs in 24093.4.patch​ that need fixing.
I'm not particularly fussed about us changing the SQL that goes through the get_meta_sql filter (I'm in favour of deprecating all filters on SQL, but that's a different discussion). We should definitely at least warn developers that it's changing, though.
@vprat: Could you upload your test cases to this ticket, so that we're all looking at the same test data?
It's worth noting that this will need testing on different versions of MySQL (at least the latest major versions from 5.0 to 5.7) - various optimisations for both JOINs and sub-queries have been introduced over the years, we can't assume that performance on the latest version is indicative of performance on all versions.
In get_sql_for_clause(), tirst time a meta_key is referenced, create a new alias and JOIN clause.
The next time you see a clause that has a meta_key that already has an alias, check to see whether the $parent_query has OR as a relation. If so, use the existing alias.
Clearly, this will need some thought, and some unit tests, and maybe some compromises regarding how many JOINS we can eliminate in case of multiply-nested queries. Who is up for a Really Fun Time writing this patch?
I've been thinking about this some more, and I think I've come up with a technique for consolidating JOINs that is along the lines of what I've laid out above. See #18105  for how it works in WP_Tax_Query. The check for "compatible aliases" will be more complicated here. I'll write some unit tests and see what I can do about a patch.
So: in each call to get_sql_for_clause(), the JOIN logic goes like this. Does this clause have any siblings that are already doing a join and are compatible with this clause (by the rules listed above)? If so, use that alias. If not, create a JOIN clause, and store the alias for use by later siblings.
So that's the first thing 24093.5.patch does. As I was writing this part of the patch - which is largely a port of  - I realized that most of the rat's nest of WP_Meta_Query::get_sql_for_clause() was a result of past developers attempting, in patchwork ways, to avoid unneccessary table joins. (See, for example, the old 'key only' queries.) With the new centralized logic for finding "compatible aliases", it became possible to rewrite the SQL generation logic of WP_Meta_Query so that it actually makes sense.
With 24093.5.patch, the query goes from 4 INNER JOINs to 2 (NOT IN can't share the join), which cuts execution time roughly in half.
It'd be great to have someone look over the logic above and the logic of the patch to make sure I'm not missing something obvious. I'm fairly confident that what I'm proposing here is solid, and it's definitely a big performance win (even if we continue to explore additional avenues for performance improvements in WP_Meta_Query).
This looks awesome @boonebgorges -- and brings up another idea that could be useful to plugins, adding a filter to the return of find_compatible_table_alias would be grand. This could allow a plugin, such as Pods, to pick up a meta fields that it covers and stores in a separate table and return a custom alias, then hook in later in the joins clauses to add it's join there. Other than that, this is the bees knees and probably wings too.
Thanks so much for having a look, sc0ttkclark! The filter idea sounds fine to me.
Overhaul SQL generating logic in WP_Meta_Query to avoid unnecessary table joins.
clause combinations that result in shared table aliases.
Remove redundant table alias check in WP_Meta_Query.
Also adds documentation for 'meta_query_find_compatible_table_alias' filter.
In the interest of having a ticket that we can close against the 4.1 milestone (and because the original ticket was about OR queries, which have been largely addressed by ), I'd like to move discussion of the subquery/AND issue over to #30044. Thanks, everyone.