sql - 优化 SQL 语句

Question

嘿，我正在运行 WordPress，可以在此处找到数据库图表:http://codex.wordpress.org/Database_Description

在执行了大量的过滤器并对核心应用了一些钩子(Hook)之后，我得到了以下查询:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts 

JOIN wp_postmeta ppmeta_beds ON (ppmeta_beds.post_id = wp_posts.ID AND
  ppmeta_beds.meta_key = 'pp-general-beds' AND ppmeta_beds.meta_value >= 2)

JOIN wp_postmeta ppmeta_baths ON (ppmeta_baths.post_id = wp_posts.ID AND
  ppmeta_baths.meta_key = 'pp-general-baths' AND ppmeta_baths.meta_value >= 3)

JOIN wp_postmeta ppmeta_furnished 
  ON (ppmeta_furnished.post_id = wp_posts.ID AND
  ppmeta_furnished.meta_key = 'pp-general-furnished' 
  AND ppmeta_furnished.meta_value = 'yes')

JOIN wp_postmeta ppmeta_pool 
  ON (ppmeta_pool.post_id = wp_posts.ID AND
  ppmeta_pool.meta_key = 'pp-facilities-pool' 
  AND ppmeta_pool.meta_value = 'yes')

JOIN wp_postmeta ppmeta_pool_type 
  ON (ppmeta_pool_type.post_id = wp_posts.ID AND
  ppmeta_pool_type.meta_key = 'pp-facilities-pool-type' 
  AND ppmeta_pool_type.meta_value 
  IN ('tennis', 'voleyball', 'basketball', 'fitness'))

JOIN wp_postmeta ppmeta_sport ON (ppmeta_sport.post_id = wp_posts.ID AND
  ppmeta_sport.meta_key = 'pp-facilities-sport' 
  AND ppmeta_sport.meta_value = 'yes') 

JOIN wp_postmeta ppmeta_sport_type ON (ppmeta_sport_type.post_id = wp_posts.ID 
  AND ppmeta_sport_type.meta_key = 'pp-facilities-sport-type' 
  AND ppmeta_sport_type.meta_value 
  IN ('tennis', 'voleyball', 'basketball', 'fitness')) 

JOIN wp_postmeta ppmeta_parking ON (ppmeta_parking.post_id = wp_posts.ID 
  AND ppmeta_parking.meta_key = 'pp-facilities-parking' 
  AND ppmeta_parking.meta_value = 'yes') 

JOIN wp_postmeta ppmeta_parking_type 
  ON (ppmeta_parking_type.post_id = wp_posts.ID 
  AND ppmeta_parking_type.meta_key = 'pp-facilities-parking-type' 
  AND ppmeta_parking_type.meta_value IN ('street', 'off-street', 'garage')) 

JOIN wp_postmeta ppmeta_garden ON (ppmeta_garden.post_id = wp_posts.ID 
  AND ppmeta_garden.meta_key = 'pp-facilities-garden' 
  AND ppmeta_garden.meta_value = 'yes') 

JOIN wp_postmeta ppmeta_garden_type 
  ON (ppmeta_garden_type.post_id = wp_posts.ID 
  AND ppmeta_garden_type.meta_key = 'pp-facilities-garden-type' 
  AND ppmeta_garden_type.meta_value IN ('private', 'communal')) 

JOIN wp_postmeta ppmeta_type ON (ppmeta_type.post_id = wp_posts.ID 
  AND ppmeta_type.meta_key = 'pp-general-type' 
  AND ppmeta_type.meta_value IN ('villa', 'apartment', 'penthouse')) 

JOIN wp_postmeta ppmeta_status ON (ppmeta_status.post_id = wp_posts.ID 
  AND ppmeta_status.meta_key = 'pp-general-status' 
  AND ppmeta_status.meta_value IN ('off-plan', 'resale')) 

JOIN wp_postmeta ppmeta_location_type 
  ON (ppmeta_location_type.post_id = wp_posts.ID 
  AND ppmeta_location_type.meta_key = 'pp-location-type' 
  AND ppmeta_location_type.meta_value 
  IN ('beachfront', 'countryside', 'town-center', 'near-the-sea', 
    'hillside', 'private-resort')) 

JOIN wp_postmeta ppmeta_price_range 
  ON (ppmeta_price_range.post_id = wp_posts.ID 
  AND ppmeta_price_range.meta_key = 'pp-general-price' 
  AND ppmeta_price_range.meta_value BETWEEN 10000 AND 50000) 

JOIN wp_postmeta ppmeta_area_range 
  ON (ppmeta_area_range.post_id = wp_posts.ID 
  AND ppmeta_area_range.meta_key = 'pp-general-area' 
  AND ppmeta_area_range.meta_value BETWEEN 50 AND 150) 

WHERE 1=1 AND (((wp_posts.post_title LIKE '%fdsfsad%') 
OR (wp_posts.post_content LIKE '%fdsfsad%'))) 
AND wp_posts.post_type = 'property' 
AND (wp_posts.post_status = 'publish' 
  OR wp_posts.post_status = 'private') 
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

太大了。谁能告诉我一种将所有这些连接优化为更少语句的方法吗？如您所见，它们都使用相同的表，但名称不同。我不是 SQL 大师，但我认为应该有办法，因为这太疯狂了 ;)

谢谢!

更新 以下是解释返回的内容:http://twitpic.com/1cd36p

Answer 1

您无法对其进行优化。您需要所有连接，因为它们是单独的过滤器，可能是由于 AND 逻辑，即您想要海滨和街边 parking 位。

最好的办法是确保您的表没有碎片。有一个关于元值、元键和 post-id 的索引。