mysql - 更快相当于 mysql inner join with sum

Question

我有一个非常长的 mysql 数据库选择。它工作得非常快，直到我添加了这个 INNER JOIN。在我添加这个 INNER JOIN 选择时间之前是 2 秒，现在大约是 20 秒......

INNER JOIN 
   (SELECT 
      accomodation_id,
      SUM(accomodation_rooms.rooms) AS total_rooms,
      SUM(accomodation_rooms.beds * accomodation_rooms.rooms) AS total_persons 

   FROM accomodation_rooms 

   GROUP BY accomodation_id) accomodation_rooms

ON 
   accomodation_rooms.accomodation_id = accomodation.id
   AND 
   accomodation_rooms.total_persons >= '".$persons."'

有人可以帮我一些更快的选择吗？？

Answer 1

SELECT 
   accomodation.id, 
   accomodation.aid, 
   accomodation.title_en, 
   accomodation.title_url_en, 
   accomodation.address, 
   accomodation.zip, 
   accomodation.stars, 
   accomodation.picture, 
   accomodation.valid_from, 
   accomodation.valid_to, 
   accomodation.latitude, 
   accomodation.longitude, 
   accomodation.city_id AS accomodation_city_id, 
   db_cities.id AS city_id, 
   db_cities.title_en AS city, 
   db_cities.title_url AS city_url, 
   db_countries.title_en AS country_title, 
   db_countries.title_url_en AS country_url, 
   accomodation_type.class AS accomodation_type_class, 
   accomodation_review_value_total.value AS review_total, 
   MIN(accomodation_price.price) AS price_from, 
   accomodation_rooms.total_persons 
FROM 
   (SELECT aid, MAX(info_date_add) AS max_info_date_add FROM accomodation GROUP BY aid) accomodation_max 
INNER JOIN accomodation 
   ON 
      accomodation_max.aid = accomodation.aid AND 
      accomodation_max.max_info_date_add = accomodation.info_date_add 
LEFT JOIN db_cities 
   ON 
      ( db_cities.id = accomodation.city_id OR (((acos(sin((db_cities.latitude*pi()/180)) * sin((accomodation.latitude*pi()/180)) + cos((db_cities.latitude*pi()/180)) * cos((accomodation.latitude*pi()/180)) * cos(((db_cities.longitude - accomodation.longitude)*pi()/180))))*180/pi())*60*1.1515*1.609344) < '20') 
JOIN db_countries 
   ON db_countries.id = accomodation.country_id 
LEFT JOIN accomodation_review_value_total 
   ON accomodation_review_value_total.accomodation_aid = accomodation.aid 
LEFT JOIN accomodation_type_value 
   ON accomodation_type_value.accomodation_id = accomodation.id 
LEFT JOIN accomodation_type 
   ON accomodation_type.id = accomodation_type_value.accomodation_type_id 
LEFT JOIN accomodation_price 
   ON 
      ( accomodation_price.accomodation_aid = accomodation.aid AND 
      accomodation_price.accomodation_price_type_id = '1' AND 
      accomodation_price.accomodation_price_cat_id = '1' ) 
LEFT JOIN accomodation_season 
   ON 
      ( accomodation_season.accomodation_aid = accomodation.aid AND 
      accomodation_season.id = accomodation_price.accomodation_season_id AND 
      ( '2013-09-25' >= accomodation_season.start_date AND 
      accomodation_season.end_date >= '2013-09-25' OR '2013-09-26' >= accomodation_season.start_date AND 
      accomodation_season.end_date >= '2013-09-26' ) ) 
INNER JOIN 
      (SELECT accomodation_id, SUM(accomodation_rooms.rooms) AS total_rooms, 
      SUM(accomodation_rooms.beds * accomodation_rooms.rooms) AS total_persons 
   FROM accomodation_rooms 
   GROUP BY accomodation_id) accomodation_rooms 
ON 
   accomodation_rooms.accomodation_id = accomodation.id AND 
   accomodation_rooms.total_persons >= '4' 
WHERE 
   db_countries.title_url_en LIKE '%country%' AND 
   db_cities.title_url LIKE '%city%' AND 
   total_rooms >= '2' AND 
   db_cities.id = '2416' 
GROUP BY accomodation.aid 
ORDER BY CASE 
   WHEN 
      accomodation.valid_to>=NOW() AND 
      accomodation.valid_from<=NOW() THEN 0 
   WHEN 
      NOW()>accomodation.valid_to AND 
      accomodation.valid_to!='0000-00-00' THEN 1 ELSE 2 END, 
review_total DESC, accomodation.title_en LIMIT 10 OFFSET 0