8

I have a custom meta query which is terribly slow or doesn’t even load till the end. With up to three arrays in 'meta_query' the query works fine, with four and up it doesn’t work anymore.

When searching for a reason I found this post but I am absolutely not familiar with custom db queries.

Any help is much appreciated! Thank you!

<?php

$args = array(
    'post_type' => $post_type,
    'posts_per_page' => -1,
    'meta_query' => array( 
        'relation' => 'OR',
        array(
           'key'=>'_author',
           'value'=> $author_single["fullname"],
           'compare' => '='
        ),
        array(
           'key'=>'_publisher',
           'value'=> $author_single["fullname"],
           'compare' => '='
        ),
        array(
           'key'=>'_contributor_1',
           'value'=> $author_single["fullname"],
           'compare' => '='
        ),
        array(
           'key'=>'_contributor_2',
           'value'=> $author_single["fullname"],
           'compare' => '='
        ),
        array(
           'key'=>'_contributor_3',
           'value'=> $author_single["fullname"],
           'compare' => '='
        )  
      )
  );   

  $posts = new WP_Query($args);

  if( $posts->have_posts() ) : while( $posts->have_posts() ) : $posts->the_post(); ?>

    <li><a href="<?php echo get_the_permalink(); ?>"><?php the_title(); ?></a></li>

  <?php endwhile; endif; ?>

– – – – –

Updated code with the additions boger made:

page.php

<?php

$args = array(
    'post_type' => $post_type,
    'posts_per_page' => -1,
    'meta_query' => array( 
        'relation' => 'OR',
        array(
           'key'=>'_author',
           'value'=> $author_single["fullname"],
           'compare' => '='
        ),
        array(
           'key'=>'_publisher',
           'value'=> $author_single["fullname"],
           'compare' => '='
        ),
        array(
           'key'=>'_contributor_1',
           'value'=> $author_single["fullname"],
           'compare' => '='
        ),
        array(
           'key'=>'_contributor_2',
           'value'=> $author_single["fullname"],
           'compare' => '='
        ),
        array(
           'key'=>'_contributor_3',
           'value'=> $author_single["fullname"],
           'compare' => '='
        )  
      )
  );   

  add_filter( 'posts_clauses', 'wpse158898_posts_clauses', 10, 2 );

  $posts = new WP_Query($args);

  if( $posts->have_posts() ) : while( $posts->have_posts() ) : $posts->the_post(); ?>

      <li><a href="<?php echo get_the_permalink(); ?>"><?php the_title(); ?></a></li>

  <?php endwhile; endif; 

  remove_filter( 'posts_clauses', 'wpse158898_posts_clauses', 10 ); ?>

functions.php

function wpse158898_posts_clauses( $pieces, $query ) {
    global $wpdb;
    $relation = isset( $query->meta_query->relation ) ? $query->meta_query->relation : 'AND';
    if ( $relation != 'OR' ) return $pieces; // Only makes sense if OR.
    $prepare_args = array();
    $key_value_compares = array();
    foreach ( $query->meta_query->queries as $meta_query ) {
        // Doesn't work for IN, NOT IN, BETWEEN, NOT BETWEEN, NOT EXISTS.
        if ( ! isset( $meta_query['value'] ) || is_array( $meta_query['value'] ) ) return $pieces; // Bail if no value or is array.
        $key_value_compares[] = '(pm.meta_key = %s AND pm.meta_value ' . $meta_query['compare'] . ' %s)';
        $prepare_args[] = $meta_query['key'];
        $prepare_args[] = $meta_query['value'];
    }
    $sql = ' JOIN ' . $wpdb->postmeta . ' pm on pm.post_id = ' . $wpdb->posts . '.ID'
        . ' AND (' . implode( ' ' . $relation . ' ', $key_value_compares ) . ')';
    array_unshift( $prepare_args, $sql );
    $pieces['join'] = call_user_func_array( array( $wpdb, 'prepare' ), $prepare_args );
    $pieces['where'] = preg_replace( '/ AND[^w]+wp_postmeta.*$/s', '', $pieces['where'] ); // Zap postmeta clauses.
    return $pieces;
}

– – –

$posts->request outputs

$args = array(
    'post_type' => $post_type,
    'posts_per_page' => -1,
    'meta_query' => array( 
        'relation' => 'OR',
        array(
           'key'=>'_author',
           'value'=> "Hanna Meier",
           'compare' => '='
        ),
        array(
           'key'=>'_publisher',
           'value'=> "Friedhelm Peters",
           'compare' => '='
        )
    )
);   

without the custom query

SELECT   wp_vacat_posts.* FROM wp_vacat_posts  INNER JOIN wp_vacat_postmeta ON (wp_vacat_posts.ID = wp_vacat_postmeta.post_id)
INNER JOIN wp_vacat_postmeta AS mt1 ON (wp_vacat_posts.ID = mt1.post_id) WHERE 1=1  AND wp_vacat_posts.post_type = 'product' AND (wp_vacat_posts.post_status = 'publish' OR wp_vacat_posts.post_status = 'private') AND ( (wp_vacat_postmeta.meta_key = '_author' AND CAST(wp_vacat_postmeta.meta_value AS CHAR) = 'Hanna Meier')
OR  (mt1.meta_key = '_publisher' AND CAST(mt1.meta_value AS CHAR) = 'Friedhelm Peters') ) GROUP BY wp_vacat_posts.ID ORDER BY wp_vacat_posts.post_date DESC   

with the custom query

SELECT   wp_vacat_posts.* FROM wp_vacat_posts  
JOIN wp_vacat_postmeta pm on pm.post_id = wp_vacat_posts.ID AND ((pm.meta_key = '_author' AND pm.meta_value = 'Hanna Meier') OR (pm.meta_key = '_publisher' AND pm.meta_value = 'Friedhelm Peters')) WHERE 1=1  AND wp_vacat_posts.post_type = 'product' AND (wp_vacat_posts.post_status = 'publish' OR wp_vacat_posts.post_status = 'private')                 AND ( (wp_vacat_postmeta.meta_key = '_author' AND CAST(wp_vacat_postmeta.meta_value AS CHAR) = 'Hanna Meier')
OR  (mt1.meta_key = '_publisher' AND CAST(mt1.meta_value AS CHAR) = 'Friedhelm Peters') ) GROUP BY wp_vacat_posts.ID ORDER BY wp_vacat_posts.post_date DESC    
  • Can you add the MySQL explaination (EXPLAIN SELECT …, on your system) from the resulting Query? – David Aug 24 '14 at 14:41
8

I've come across this problem and it seems MySQL doesn't deal well with the multiple joins to the same table (wp_postmeta) and OR-ed WHERE that WP generates here. I dealt with it by rewriting the join and where as mentioned in the post you link to - here's a version that should work in your case (updated for WP 4.1.1) (updated for WP 4.2.4):

function wpse158898_posts_clauses( $pieces, $query ) {
    global $wpdb;
    $relation = isset( $query->meta_query->relation ) ? $query->meta_query->relation : 'AND';
    if ( $relation != 'OR' ) return $pieces; // Only makes sense if OR.
    $prepare_args = array();
    $key_value_compares = array();
    foreach ( $query->meta_query->queries as $key => $meta_query ) {
        if ( ! is_array( $meta_query ) ) continue;
        // Doesn't work for IN, NOT IN, BETWEEN, NOT BETWEEN, NOT EXISTS.
        if ( $meta_query['compare'] === 'EXISTS' ) {
            $key_value_compares[] = '(pm.meta_key = %s)';
            $prepare_args[] = $meta_query['key'];
        } else {
            if ( ! isset( $meta_query['value'] ) || is_array( $meta_query['value'] ) ) return $pieces; // Bail if no value or is array.
            $key_value_compares[] = '(pm.meta_key = %s AND pm.meta_value ' . $meta_query['compare'] . ' %s)';
            $prepare_args[] = $meta_query['key'];
            $prepare_args[] = $meta_query['value'];
        }
    }
    $sql = ' JOIN ' . $wpdb->postmeta . ' pm on pm.post_id = ' . $wpdb->posts . '.ID'
        . ' AND (' . implode( ' ' . $relation . ' ', $key_value_compares ) . ')';
    array_unshift( $prepare_args, $sql );
    $pieces['join'] = call_user_func_array( array( $wpdb, 'prepare' ), $prepare_args );
    // Zap postmeta clauses.
    $wheres = explode( "\n", $pieces[ 'where' ] );
    foreach ( $wheres as &$where ) {
        $where = preg_replace( array(
            '/ +\( +' . $wpdb->postmeta . '\.meta_key .+\) *$/',
            '/ +\( +mt[0-9]+\.meta_key .+\) *$/',
            '/ +mt[0-9]+.meta_key = \'[^\']*\'/',
        ), '(1=1)', $where );
    }
    $pieces[ 'where' ] = implode( '', $wheres );
    $pieces['orderby'] = str_replace( $wpdb->postmeta, 'pm', $pieces['orderby'] ); // Sorting won't really work but at least make it not crap out.
    return $pieces;
}

and then around your query:

  add_filter( 'posts_clauses', 'wpse158898_posts_clauses', 10, 2 );
  $posts = new WP_Query($args);
  remove_filter( 'posts_clauses', 'wpse158898_posts_clauses', 10 );

Addendum:

The fix for this, ticket 24093, didn't make it into 4.0 (plus it didn't fix this issue anyway), so originally I attempted a generalized version of the above but it is too flakey really to attempt such a solution so I've removed it...

| improve this answer | |
  • Thank you very much for your help! I will give it a try and let you know. – user1706680 Aug 24 '14 at 18:12
  • Your code seems to work really well! I didn’t check it with all my posts yet but with the ones I have so far it’s really quick! Thanks a lot again—I really appreciate your help! – user1706680 Aug 30 '14 at 19:53
  • 1
    Wohooo, it works! May I thank you with a small donation? – user1706680 Sep 3 '14 at 15:08
  • 1
    Hurray, at last! And that's very kind of you to offer - if you like, donate to your favorite charity (as they say on TV) - you contributed too by correcting my answer, and I hope to generalize it in the not too distant future (note non-committing fuzziness!) given that the WP fix won't make 4.0. – bonger Sep 3 '14 at 15:38
  • 1
    @bonger Don't worry, not your fault. – vee Aug 15 '15 at 20:04
6

The short answer is, meta data in WordPress is not intended to be used for relational data. Fetching posts by several conditions to its meta data is not the idea behind meta data. Therefore the queries, table structures and indexes are not optimized for that.

The longer answer:

What your Meta-Query results in is something like that:

SELECT   wp_4_posts.* FROM wp_4_posts  
INNER JOIN wp_4_postmeta ON (wp_4_posts.ID = wp_4_postmeta.post_id)
INNER JOIN wp_4_postmeta AS mt1 ON (wp_4_posts.ID = mt1.post_id)
INNER JOIN wp_4_postmeta AS mt2 ON (wp_4_posts.ID = mt2.post_id)
INNER JOIN wp_4_postmeta AS mt3 ON (wp_4_posts.ID = mt3.post_id)
INNER JOIN wp_4_postmeta AS mt4 ON (wp_4_posts.ID = mt4.post_id) 
WHERE 1=1  
AND wp_4_posts.post_type = 'post' 
AND (wp_4_posts.post_status = 'publish' OR wp_4_posts.post_status = 'private') 
AND ( (wp_4_postmeta.meta_key = '_author' AND CAST(wp_4_postmeta.meta_value AS CHAR) = 'Test')
OR  (mt1.meta_key = '_publisher' AND CAST(mt1.meta_value AS CHAR) = 'Test')
OR  (mt2.meta_key = '_contributor_1' AND CAST(mt2.meta_value AS CHAR) = 'Test')
OR  (mt3.meta_key = '_contributor_2' AND CAST(mt3.meta_value AS CHAR) = 'Test')
OR  (mt4.meta_key = '_contributor_3' AND CAST(mt4.meta_value AS CHAR) = 'Test') ) GROUP BY wp_4_posts.ID ORDER BY wp_4_posts.post_date DESC

Let's have a look how MySQL handles this query (EXPLAIN):

    id      select_type     table           type    possible_keys                   key                     key_len ref                             rows    Extra
    1       SIMPLE          wp_4_posts      range   PRIMARY,type_status_date        type_status_date        124     NULL                            5       Using where; Using temporary; Using filesort
    1       SIMPLE          wp_4_postmeta   ref     post_id,meta_key                post_id                  8      wordpress.wp_4_posts.ID         1
    1       SIMPLE          mt1             ref     post_id,meta_key                post_id                  8      wordpress.wp_4_posts.ID         1
    1       SIMPLE          mt2             ref     post_id,meta_key                post_id                  8      wordpress.mt1.post_id           1       Using where
    1       SIMPLE          mt3             ref     post_id,meta_key                post_id                  8      wordpress.wp_4_posts.ID         1
    1       SIMPLE          mt4             ref     post_id,meta_key                post_id                  8      wordpress.wp_4_postmeta.post_id 1       Using where

Now what you can see, MySQL make a select on wp_posts and joins 5 times the table wp_postmeta. The type ref says that MySQL has to examine all rows in this table, matching the index (post_id, meta_key) comparing a non indexed column value with your where clause, and that for each combination of rows from the previous table. The MySQL manual says: »If the key that is used matches only a few rows, this is a good join type.« And that is the first problem: on a average WordPress system, the number of post-metas per post can easily grow up to 30-40 records or more. The other possible key meta_key grows with your post count. So if you have 100 posts and each has a _publisher meta, there are 100 rows with this value as meta_key in wp_postmeta, of course.

To handle all these possible results, mysql creates a temporary table (using temporary). If this table becomes to large, the server usually stores it on the disk instead of memory. Another possible bottleneck.

Possible solutions

As described in the existing answers, you could try to optimize the query on your own. That may work well for your concerns, but may lead to trouble as the post/postmeta tables grows.

But if you want to use the WordPress Query API you should consider to use Taxonomies to store data you want to search posts by.

| improve this answer | |
  • Ah so that "explains" why you get the hit the first time the query runs, but not on subsequent immediate runs - great stuff! But I can't agree with your point about using taxonomies instead, that seems unnatural at best, if not unfeasible. Why shouldn't you query metadata relationally? - WP has a lot code to deal exactly with that usage, and does so itself! To me it's simply a bug in WP - it should be able to generate much better code here, rather than moronically generating join after join. – bonger Aug 25 '14 at 11:45
  • There's a WP ticket (3 years old!) on this 24093 which has a patch that fixes it by using sub-queries. Won't make it into 4.0 though... – bonger Aug 25 '14 at 16:24
3

This might be a little late to the game but I ran into the same issue. When building a plugin to handle searching for properties, my advanced search option would query as many as 20 different meta entries for each post to find those matching the search criteria.

My solution was to query the DB directly using the $wpdb global. I queried each meta entry individually and stored the post_ids of the posts that match each criteria. I then did an intersect on each of the matched sets to come up with the post_ids that matched all of the criteria.

My case was somewhat simple because I did not have any OR elements that I needed to account for but they could fairly easily be included. Depending on how complex your query is, this is a working and fast solution. Although, I admit it is a poor option compared to being able to do a true relational query.

Below code has been simplified greatly from what I used but you can get the idea from it.

class property_search{

public function get_results($args){
    $potential_ids=[];
    foreach($args as $key=>$value){
        $potential_ids[$key]=$this->get_ids_by_query("
            SELECT post_id
            FROM wp_postmeta
            WHERE meta_key = '".$key."'
            AND CAST(meta_value AS UNSIGNED) > '".$value."'
        ");//a new operator would need to be created to handle each type of data and comparison. 
    }

    $ids=[];
    foreach($potential_ids as $key=>$temp_ids){
        if(count($ids)==0){
            $ids=$temp_ids;
        }else{
             $ids=array_intersect($ids,$temp_ids);
        }
    }

    $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
    $args = array(
        'posts_per_page'=> 50,
        'post_type'=>'property',
        'post_status'=>'publish',
        'paged'=>$paged,
        'post__in'=>$ids,
    );
    $search = new WP_Query($args);
    return $search;
}

public function get_ids_by_query($query){
    global $wpdb;
    $data=$wpdb->get_results($query,'ARRAY_A');
    $results=[];
    foreach($data as $entry){
        $results[]=$entry['post_id'];
    }
    return $results;
}

}
| improve this answer | |
1

wp_postmeta has inefficient indexes. Here is a discussion of such, plus recommended cures:

http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

| improve this answer | |
0

ORs are really expensive.

You have too many keys, but let's assume you can't change that now. The other thing you can do, without too much coding, is changing the number of posts you are getting, change 'posts_per_page' to 10, or a bigger number, and see how much does the performance changes.

| improve this answer | |
  • Thanks for the hint – currently I only have one post, so changing post_per_pagewon’t change anything. – user1706680 Aug 23 '14 at 20:23
  • Ok, if you are just starting then, maybe you can change the number of keys. What do you think? – Tomás Cot Aug 23 '14 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.