SELECT
s_id, s_name, s_name_eng, s_open_time, s_tel, s_comment_title, s_comment, s_charge_range, s_status ,
g1.g_id AS g1_id, g1.g_name_eng AS g1_name_eng, g1.g_img_01 AS g1_img, g1.g_img_alt AS g1_alt ,
g2.g_id AS g2_id, g2.g_name_eng AS g2_name_eng, g2.g_img_01 AS g2_img, g2.g_img_alt AS g2_alt ,
g3.g_id AS g3_id, g3.g_name_eng AS g3_name_eng, g3.g_img_01 AS g3_img, g3.g_img_alt AS g3_alt ,
(SELECT m_val FROM mst_option WHERE m_type = 'SHOP_TYPE' AND m_key = s_type) AS s_type_name ,
(SELECT ad_name FROM mst_area_detail WHERE ad_id = s_area_detail_id) AS s_area_detail_name
FROM
shop_info
LEFT OUTER JOIN
girl_info g1 ON g1.g_s_id = s_id AND g1.g_rank = 1
LEFT OUTER JOIN
girl_info g2 ON g2.g_s_id = s_id AND g2.g_rank = 2
LEFT OUTER JOIN
girl_info g3 ON g3.g_s_id = s_id AND g3.g_rank = 3
WHERE 1 AND
s_area_id = '101' AND s_start_ymd <= '2016-10-12' AND '2016-10-12' <= s_end_ymd
ORDER BY s_status DESC, s_update_date DESC LIMIT 0, 20
위와 같은 쿼리를 만들었는데 속도가 엄청 느려서 수정을 해야 할듯 한데 뭐가 문제 인지를 잘 모르겠습니다.
일단 left outer join 부분을 전부 없애면 빨리지는 것을 확인해서 left outer join쪽이 문제 인듯한데..
어떻게 수정하면 빨라질까요??
|