꿈꾸는 개발자, DBA 커뮤니티 구루비

조회쿼리의 속도가 너무 느린데 보완가능할까요?

by mjee [MySQL] mysql select문 [2018.07.17 15:21:24]

안녕하세요!

일정확인 기능 구현을 위해 아래와 같은 쿼리를 작성했습니다.

SELECT * FROM (
select 
	DISTINCT D.SELECTED_DATE
FROM 
	t_ERR A, 
	(SELECT * FROM 
	(SELECT ADDDATE('2018-07-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) SELECTED_DATE FROM
	 (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
	 (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
	 (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
	 (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
	 (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE SELECTED_DATE BETWEEN '2018-07-01' AND '2018-07-31') D
where 
	FROM_UNIXTIME(SDATE, '%Y-%m-%d') <= D.SELECTED_DATE
	AND FROM_UNIXTIME(EDATE, '%Y-%m-%d') >= D.SELECTED_DATE
UNION
select 
	DISTINCT D.SELECTED_DATE
FROM 
	t_WORK A, 
	(SELECT * FROM 
	(SELECT ADDDATE('2018-07-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) SELECTED_DATE FROM
	 (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
	 (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
	 (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
	 (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
	 (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE SELECTED_DATE BETWEEN '2018-07-01' AND '2018-07-31') D
where 
	FROM_UNIXTIME(SDATE, '%Y-%m-%d') <= D.SELECTED_DATE
	AND FROM_UNIXTIME(EDATE, '%Y-%m-%d') >= D.SELECTED_DATE
UNION
select 
	DISTINCT D.SELECTED_DATE
FROM 
	t_CHK A, 
	(SELECT * FROM 
	(SELECT ADDDATE('2018-07-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) SELECTED_DATE FROM
	 (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
	 (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
	 (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
	 (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
	 (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE SELECTED_DATE BETWEEN '2018-07-01' AND '2018-07-31') D
where 
	FROM_UNIXTIME(SDATE, '%Y-%m-%d') <= D.SELECTED_DATE
	AND FROM_UNIXTIME(EDATE, '%Y-%m-%d') >= D.SELECTED_DATE
UNION
select 
	DISTINCT D.SELECTED_DATE
FROM 
	t_CSR_REQ A, 
	(SELECT * FROM 
	(SELECT ADDDATE('2018-07-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) SELECTED_DATE FROM
	 (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
	 (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
	 (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
	 (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
	 (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE SELECTED_DATE BETWEEN '2018-07-01' AND '2018-07-31') D
where 
	FROM_UNIXTIME(SDATE, '%Y-%m-%d') <= D.SELECTED_DATE
	AND FROM_UNIXTIME(EDATE, '%Y-%m-%d') >= D.SELECTED_DATE
	
	GROUP BY D.SELECTED_DATE
) G

설명 드리자면

시작일	종료일
2018-07-04	2018-07-05
2018-07-10	2018-07-11

위와같이 시작일 종료일이 있는 테이블 4개를 가지고 시작일 종료일에 속한 날짜를 가져오도록 했습니다. (금월한정)

위 테이블의 경우 아래와같이 나오도록요

2018-07-03

2018-07-04

2018-07-10

2018-07-11

겹치는 날짜는 안보이게 했습니다.

날짜 비교를 위해 날짜 가져오는 서브쿼리를 사용했는데 이때문에 쿼리가 많이 느려진것같습니다. (위 쿼리를 실행하면 약 6초가 걸립니다)

이를 보완할 방법이 있을까요?

더운날씨에 모두 건강 조심하세요~

by 마농 [2018.07.17 16:21:40]

SELECT DISTINCT a.selected_date
  FROM (SELECT ADDDATE('2017-07-01', t1*10 + t0) selected_date
          FROM (SELECT 0 t1
                UNION ALL SELECT 1
                UNION ALL SELECT 2
                UNION ALL SELECT 3
                ) t1
         CROSS JOIN
               (SELECT 0 t0
                UNION ALL SELECT 1
                UNION ALL SELECT 2
                UNION ALL SELECT 3
                UNION ALL SELECT 4
                UNION ALL SELECT 5
                UNION ALL SELECT 6
                UNION ALL SELECT 7
                UNION ALL SELECT 8
                UNION ALL SELECT 9
                ) t0
        ) a
 INNER JOIN
       (SELECT sdate, edate
          FROM t_err
         WHERE sdate <  UNIX_TIMESTAMP('2018-08-01')
           AND edate >= UNIX_TIMESTAMP('2018-07-01')
         UNION ALL
        SELECT sdate, edate
          FROM t_work
         WHERE sdate <  UNIX_TIMESTAMP('2018-08-01')
           AND edate >= UNIX_TIMESTAMP('2018-07-01')
         UNION ALL
        SELECT sdate, edate
          FROM t_chk
         WHERE sdate <  UNIX_TIMESTAMP('2018-08-01')
           AND edate >= UNIX_TIMESTAMP('2018-07-01')
         UNION ALL
        SELECT sdate, edate
          FROM t_csr_req
         WHERE sdate <  UNIX_TIMESTAMP('2018-08-01')
           AND edate >= UNIX_TIMESTAMP('2018-07-01')
        ) b
    ON from_unixtime(b.sdate, '%Y-%m-%d') <= a.selected_date
   AND from_unixtime(b.edate, '%Y-%m-%d') >= a.selected_date
 WHERE a.selected_date <= '2018-07-31'
;

by mjee [2018.07.17 17:32:10]

훨씬 나은방법이 있었네요 간단하고...

마농님 여러번 감사드립니다

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변