Slow WP_Query for custom post type

Question

I have a real estate theme with custom post: properties. Post has many meta values such as price and taxonomy such as location. I created search engine for this but queries take ridiculous amount of time to execute.

I have only 50 properties in the database, but wp_postmeta table has 20044 rows.

Sample query, that take over 30s:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_relationships AS tt1
ON (wp_posts.ID = tt1.object_id)
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1
ON ( wp_posts.ID = mt1.post_id )
INNER JOIN wp_postmeta AS mt2
ON ( wp_posts.ID = mt2.post_id )
INNER JOIN wp_postmeta AS mt3
ON ( wp_posts.ID = mt3.post_id )
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (6)
AND tt1.term_taxonomy_id IN (19,287) )
AND ( wp_postmeta.meta_key = 'featured'
AND ( ( mt1.meta_key = 'area'
AND CAST(mt1.meta_value AS SIGNED) <= '100000' )
AND ( mt2.meta_key = 'price'
AND CAST(mt2.meta_value AS SIGNED) <= '4433356' )
AND ( mt3.meta_key = 'offer_order_status'
AND mt3.meta_value = 'active' ) ) )
AND wp_posts.post_type = 'property'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 DESC
LIMIT 0, 12

And here is the $args table passed to the WP_Query:

array (size=7)
  'post_type' => string 'property' (length=18)
  'paged' => int 1
  'meta_key' => string 'featured' (length=21)
  'orderby' => string 'meta_value_num' (length=14)
  'order' => string 'DESC' (length=4)
  'tax_query' => 
    array (size=3)
      0 => 
        array (size=3)
          'taxonomy' => string 'property_type' (length=13)
          'field' => string 'id' (length=2)
          'terms' => string '6' (length=1)
      1 => 
        array (size=3)
          'taxonomy' => string 'transaction_type' (length=16)
          'field' => string 'id' (length=2)
          'terms' => 
            array (size=2)
              0 => string '19' (length=2)
              1 => string '287' (length=3)
      'relation' => string 'AND' (length=3)
  'meta_query' => 
    array (size=4)
      0 => 
        array (size=4)
          'key' => string 'area' (length=17)
          'value' => int 100000
          'type' => string 'NUMERIC' (length=7)
          'compare' => string '<=' (length=2)
      1 => 
        array (size=4)
          'key' => string 'price' (length=18)
          'value' => int 4433356
          'type' => string 'NUMERIC' (length=7)
          'compare' => string '<=' (length=2)
      'relation' => string 'AND' (length=3)
      2 => 
        array (size=3)
          'key' => string 'offer_order_status' (length=31)
          'value' => string 'active' (length=6)
          'compare' => string '=' (length=1)

Here is EXPLAIN result:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  wp_term_relationships   ref     PRIMARY,term_taxonomy_id    term_taxonomy_id    8   const   19  Using where; Using index; Using temporary; Using filesort
1   SIMPLE  wp_posts    eq_ref  PRIMARY,post_name,type_status_date,post_parent,post_author  PRIMARY     8   wp_real_estate3.wp_term_relationships.object_id     1   Using where
1   SIMPLE  tt1     ref     PRIMARY,term_taxonomy_id    PRIMARY     8   wp_real_estate3.wp_term_relationships.object_id     2   Using where; Using index
1   SIMPLE  mt3     ref     post_id,meta_key    post_id     8   wp_real_estate3.wp_term_relationships.object_id     12  Using where
1   SIMPLE  mt1     ref     post_id,meta_key    post_id     8   wp_real_estate3.wp_term_relationships.object_id     12  Using where
1   SIMPLE  mt2     ref     post_id,meta_key    post_id     8   wp_real_estate3.wp_term_relationships.object_id     12  Using where
1   SIMPLE  wp_postmeta     ref     post_id,meta_key    post_id     8   wp_real_estate3.wp_term_relationships.object_id     12  Using where

I've read many related questions but can't find any answer that describes hot to change the query to seed it up.

Thanks

EDIT:

I've created custom SQL that seems to return the same values with the speed of 0.01s:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_relationships AS tt1
ON (wp_posts.ID = tt1.object_id)
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (6)
AND tt1.term_taxonomy_id IN (19,287) )
AND ( wp_postmeta.meta_key = 'apartment_wp_featured' )
AND wp_posts.post_type = 'apartment_property'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
AND wp_posts.ID IN (
                        SELECT post_id from wp_postmeta
                        WHERE ((meta_key = 'apartment_wp_area' AND CAST(meta_value AS SIGNED) < '100000')
                        or (meta_key = 'apartment_wp_price' AND CAST(meta_value AS SIGNED) < '4433356')
                        or (meta_key = 'apartment_wp_offer_order_status' AND meta_value = 'active'))
                )
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 DESC
LIMIT 0, 12

Now I'll try to add all meta_query (which cause performance issues) as a custom SQL with add_filter( 'posts_where' , 'posts_where_statement' );

Answer 1

That's a known problem with WordPress and searches involving postmeta.

What I'd try to do is to create a new taxonomy for "things" you're usually searching the most:

• offer_order_status seems the better candidate, as it can be active or not. I'd create a taxonomy with two elements. That would reduce the time of your search considerably
• If that is not enough, the next step I'd probably try is to define "buckets" for the other two "meta" you're using and create taxonomies for them: area and price. Buckets can be created in to different ways: you could have a "size" taxonomy that was > 100000, another one > 200000, etc, and then for prices, you may want to do < 100000, > 100000 && < 200000. That really depends on how you plan to search.

The whole point for step two is that you can hook into the save action of the post to "read" what is the area/size for that property, and then pick the right taxonomies for it and add them automatically in the backend. By doing that, you get the benefit of: a) having the taxonomies always in sync with the actual meta value (you don't want to have people adding "the same" or similar information more than once). b) you can change the "buckets" of the taxonomies at any time to fullfill your "new" needs: as you have the real value for those fields, you'll just need to "save" again all posts to get the right taxonomies (and you can do that programatically). c) taxonomy-based search is muuuuuuuch faster than meta search (is an indexed&relatively small table, compared to postmeta)

---

A second approach, that won't work that well for this case, but is still an option, is to add indexes to the postmeta table, indexing by post_id, meta_key and meta_value. That will also speed up the query, by using indexes, but probably not that much.

But as it's much easier to implement than the previous one, I'd probably try to implement this second option first and see how it goes.

Answer 2

Assuming the amount of changes is not big per day you can create a "shadow DB" in a format nicer to query, for example each attribute has its own column, and update it on every post save.

The advantage for this approach is that you do not have to change anything in your post/meta/terms usage, no change in admin, and none in front end.The disadvantage is that you do need to write the code to adjust the data on every post and tem save which will create some more work for you in writting the code, and to the DB server on each update.