[wp-trac] [WordPress Trac] #56821: meta_query late row lookup for performance improvement

[WordPress Trac]

#56821: meta_query late row lookup for performance improvement
-------------------------------+-------------------------------
 Reporter:  brmoore252         |      Owner:  (none)
     Type:  enhancement        |     Status:  new
 Priority:  normal             |  Milestone:  Awaiting Review
Component:  Posts, Post Types  |    Version:
 Severity:  normal             |   Keywords:  changes-requested
  Focuses:                     |
-------------------------------+-------------------------------
 Is it possible to do a late row lookup for meta_query to make large
 postmeta sets run much faster?

 In some benchmarking I've done with load testing, queries that would take
 8s to load with the current meta_query ended up loading in 500ms after
 implementing late row lookup.

 My suggestion would be to modify the get_posts function in the WP_Query
 class to have a flag pass through to use late lookup, like passing through
 `"meta_late_lookup"=>true`:

 {{{#!php
 <?php
 if ( ! empty( $this->meta_query->queries ) ) {
         $clauses = $this->meta_query->get_sql( 'post', $wpdb->posts, 'ID',
 $this );
         if(isset($q['meta_query_late_lookup']) &&
 $q['meta_query_late_lookup']){
                 // perform a late lookup instead of a join
                 $clauses['where'] = ' AND ID in (SELECT post_id FROM
 '.$wpdb->postmeta.' WHERE 1=1 '.$clauses['where'].')';
         } else {
                 $join   .= $clauses['join'];
         }
         $where  .= $clauses['where'];
 }
 }}}


 At the moment, this can be accomplished with a filter like so:

 {{{#!php
 <?php
 add_filter('get_meta_sql', 'meta_sql_late_row_lookup', NULL, 6);
 function meta_sql_late_row_lookup($sql, $queries, $type, $table, $column,
 $context){
         $meta_table = _get_meta_table( $type );
         $sql = array('where'=>' AND ID in (SELECT post_id FROM
 '.$meta_table.' WHERE 1=1 '.$sql['where'].')');
         return $sql;
 }
 }}}

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/56821>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform