我有一个定制贴子：属性的房地产主题。Post有许多元值，如价格和分类法，如位置。我为此创建了搜索引擎，但查询需要花费大量的时间来执行。

我在数据库中只有50个属性，但wp\\U Posteta表有20044行。

超过30秒的示例查询：

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

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)

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

谢谢

EDIT:

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