[wp-trac] [WordPress Trac] #30044: Use subqueries for AND clauses in WP_Meta_Query

[WordPress Trac]

#30044: Use subqueries for AND clauses in WP_Meta_Query
--------------------------+-----------------------------
 Reporter:  boonebgorges  |       Owner:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Future Release
Component:  Query         |     Version:
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  performance
--------------------------+-----------------------------

Comment (by p51labs):

 The wp-query-patch plugin has a replacement for the get_sql_for_query
 function in wp-includes/meta.php. I opted for separate queries in this
 solution over joins and sub queries because there are no mysql version
 issues and they are faster. The wp-query-patch-test-plugin will create a
 custom post type called 'Test Posts' and will then populate the post type
 with 10,000 entries, each with 11 custom fields and values randomly chosen
 from 0-100. When the list page is loaded it will run a query against the
 test posts using a large nested meta query to test with. Below are some
 examples of execution times using the attached plugins.

 WordPress: AND ~ 110s OR ~ Timeout
 Patch (all queries totaled): AND ~ 100ms OR ~ 100ms

 Obviously these show a dramatic difference in execution time. We have been
 doing something similar with the Piklist plugin
 (https://wordpress.org/plugins/piklist/) for the last 2+ years and have
 been very pleased with the speed, allowing even the most complex of
 queries.

 Let me know if there are any questions, I thought this would be a good
 place to kick things off?

--
Ticket URL: <https://core.trac.wordpress.org/ticket/30044#comment:3>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform