[SQL] 쿼리 튜닝 : select 문 안에 서브쿼리

원래 쿼리 ) 

SELECT DATE_FORMAT(CONCAT(JOB_DATE,JOB_TIME),'%Y-%m-%d %H:%i:%s') AS collect_date

            , CAPACITY_ID

            , B.pattern_code_name AS CAPACITY_NAME

            , CAPACITY_SUB_ID, 

            , B2.pattern_code_name AS CAPACITY_SUB_NAME

            

        FROM  tb_data A, tb_common_code B, tb_common_code B2

        WHERE 1=1

         AND job_date >= "20120827"

         AND job_date <= "20120828"

         AND CONCAT(job_date,job_time) >= "20120827000000"

         AND CONCAT(job_date,job_time) < "20120828000000"

         AND job_time <> ''

          AND A.capacity_id = B.pattern_code_id AND A.capacity_sub_id = B2.pattern_code_id

보다시피 같은 테이블을 두번 조인을 하는 

굉장히 비효율적인 쿼리다

특히 tb_common_code 에 없는 id가 걸리면

속도는 비정상적으로 느려진다

4초 걸리던 아이가 

23분이 되는 기적 (쨔란)

수정1) 날짜지정을 서브쿼리로 빼서 데이터범위를 줄였다. 

SELECT DATE_FORMAT(CONCAT(JOB_DATE,JOB_TIME),'%Y-%m-%d %H:%i:%s') AS collect_date

            , CAPACITY_ID

            , B.pattern_code_name AS CAPACITY_NAME

            , CAPACITY_SUB_ID, 

            , B2.pattern_code_name AS CAPACITY_SUB_NAME

        FROM (SELECT *  FROM  tb_data A

              WHERE 1=1        

              AND job_date >= "20120827"

              AND job_date <= "20120828"

              AND CONCAT(job_date,job_time) >= "20120827000000"

              AND CONCAT(job_date,job_time) < "20120828000000"

              AND job_time <> ''

                  ) A

      , tb_common_code B, tb_common_code B2

        WHERE 1=1

          AND A.capacity_id = B.pattern_code_id AND A.capacity_sub_id = B2.pattern_code_id

20분

별로 나아진점이 없엇다

최종수정) 

SELECT  DATE_FORMAT(CONCAT(JOB_DATE,JOB_TIME),'%Y-%m-%d %H:%i:%s') AS collect_date

            , CAPACITY_ID

            #, B.pattern_code_name AS CAPACITY_NAME

            ,(select pattern_code_name from tb_common_code code where 1=1 and  A.capacity_id = code.pattern_code_id ) as CAPACITY_NAME

            , CAPACITY_SUB_ID

            #, B2.pattern_code_name AS CAPACITY_SUB_NAME

            ,(select pattern_code_name from tb_common_code code where 1=1 and  A.capacity_sub_id = code.pattern_code_id) AS CAPACITY_SUB_NAME

        FROM (SELECT * FROM tb_data 

         WHERE 1=1        

         AND job_date >= "20120827"

         AND job_date <= "20120828"

         AND CONCAT(job_date,job_time) >= "20120827000000"

         AND CONCAT(job_date,job_time) < "20120828000000"

         AND job_time <> ''

          ) A

      #, tb_common_pattern_code B, tb_common_pattern_code B2

        WHERE 1=1

         # AND A.capacity_id = B.pattern_code_id AND A.capacity_sub_id = B2.pattern_code_id

20분 -> 14초

이거시 10년차 짬밥이다. 를 보여주는 서브쿼리 사용의 예/.

튜닝 잘하고싶다...