meta_query late row lookup for performance improvement

[brmoore252]

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
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
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;
}