MySql Order by로 인한 속도저하 질문드립니다. 0 2 814

by Atreyu MYSQL ORDERBY [2018.07.20 16:33:56]


안녕하세요. 이번에 MYSQL을 사용하게 된 초보개발자입니다!

다름이 아니라 제가 질문드리고 싶은건 ORDER BY에 의한 속도저하인데요.

Key로 형성되어 있는 PI_NUMB라는 컬럼을 가지고 정렬을 해야 하는데요.

맨 바깥쪽의 ORDER BY T.PI_NUMB DESC를 사용해서 정렬을 하면 쿼리가 10초가 넘어가 버립니다..

안쪽의 ORDER BY O.PI_NUMB DESC를 사용하면 속도는 1초안쪽으로 걸리나 정렬이 틀어집니다..

어느 부분이 잘못된 건지 감이 잡히지 않아서 질문 드립니다!

SELECT T.PI_NUMB,
	       T.COMP_NAME,
	       T.O_DATE,
	       T.D_DATE,
	       T.P_CODE,
		   T.PW_NUMB,
	       T.PRS_NUMB,
	       T.KAI_PRS_NUMB,
	       T.PRS_CODE,
	       T.OUT_MODE,
	       T.EQU_MODE,
	       T.EQU_CODE,
	       T.PW_USER,
	       T.PW_COMP,
	       DATE_FORMAT(T.PW_SDATE, '%Y-%m-%d %H:%i') PW_SDATE,
	       DATE_FORMAT(T.PW_EDATE, '%Y-%m-%d %H:%i') PW_EDATE,
	       CASE
		       WHEN T.PW_STATE = '03' THEN TIMESTAMPDIFF(MINUTE, T.PW_SDATE, T.PW_EDATE)
		       WHEN T.PW_STATE = '02' THEN TIMESTAMPDIFF(MINUTE, T.PW_SDATE, NOW())
	       ELSE 0
	       END PW_WORK_TIME,
	       TIMESTAMPDIFF(MINUTE, (
	       						SELECT PW_EDATE
	       						FROM ORDER_WORK
	       						WHERE PW_EDATE<= T.PW_SDATE
	       						AND PI_NUMB = T.PI_NUMB
	       						ORDER BY PW_EDATE DESC
	       						LIMIT 0, 1
	       						), T.PW_SDATE) PW_WAIT_TIME,
	       T.PW_STATE,
	       (SELECT CODE_NAME FROM CODE WHERE CODE_ID = 'PW_STATE' AND CODE_VALUE = T.PW_STATE) AS PW_STATE_NAME,
	       P.PRS_NAME AS PRS_CODE_NAME,
	       E.EQU_NAME,
	       U.U_NAME AS PW_USER_NAME,
	       C.COMP_NAME AS PW_COMP_NAME,
	       TIMESTAMPDIFF(MINUTE, OWS.STOP_SDATE, OWS.STOP_EDATE) STOP_MIN,
	       (SELECT CODE_NAME FROM CODE WHERE CODE_ID = 'STOP_NOTE' AND CODE_VALUE = OWS.STOP_NOTE) STOP_NOTE_NAME,
	       OWS.REG_DATE
	FROM
	(
		SELECT O.PI_NUMB,
		       C.COMP_NAME,
		       O.O_DATE,
		       O.D_DATE,
		       O.P_CODE,
			   W.PW_NUMB,
		       W.PRS_NUMB,
		       W.KAI_PRS_NUMB,
		       W.PRS_CODE,
		       W.OUT_MODE,
		       W.EQU_MODE,
		       W.EQU_CODE,
		       W.PW_USER,
		       W.PW_COMP,
		       W.PW_SDATE,
		       W.PW_EDATE,
		       W.PW_STATE
		FROM ORDER_INFO O
		LEFT JOIN COMP_INFO C ON C.COMP_CODE = O.COMP_CODE
		LEFT JOIN ORDER_WORK W ON O.PI_NUMB = W.PI_NUMB 
		WHERE O.O_ENABLE = 'Y'
		AND W.PW_STATE = '03'
-- 		AND O.O_TYPE = '01'
-- 		ORDER BY O.PI_NUMB DESC
	) T
	LEFT JOIN PROCESS_INFO P ON P.PRS_CODE = T.PRS_CODE
	LEFT JOIN EQUIPMENT_INFO E ON E.EQU_CODE = T.EQU_CODE
	LEFT JOIN USER_INFO U ON U.U_ID = T.PW_USER
	LEFT JOIN COMP_INFO C ON C.COMP_CODE = T.PW_COMP
	LEFT JOIN ORDER_WORK_STOP OWS ON T.PW_NUMB = OWS.PW_NUMB AND T.PI_NUMB = OWS.PI_NUMB
-- 	ORDER BY T.PI_NUMB DESC
LIMIT 0, 50

 

by Atreyu [2018.07.20 16:53:48]
해결했습니다. JOIN 전에 ORDER BY를 먼저하고 하면 해결되네요! 간단한 걸 너무 급해서 올려버렸나 봅니다!
SELECT T.PI_NUMB,
       T.COMP_NAME,
       T.O_DATE,
       T.D_DATE,
       T.P_CODE,
	   T.PW_NUMB,
       T.PRS_NUMB,
       T.KAI_PRS_NUMB,
       T.PRS_CODE,
       T.OUT_MODE,
       T.EQU_MODE,
       T.EQU_CODE,
       T.PW_USER,
       T.PW_COMP,
       DATE_FORMAT(T.PW_SDATE, '%Y-%m-%d %H:%i') PW_SDATE,
       DATE_FORMAT(T.PW_EDATE, '%Y-%m-%d %H:%i') PW_EDATE,
       CASE
	       WHEN T.PW_STATE = '03' THEN TIMESTAMPDIFF(MINUTE, T.PW_SDATE, T.PW_EDATE)
	       WHEN T.PW_STATE = '02' THEN TIMESTAMPDIFF(MINUTE, T.PW_SDATE, NOW())
       ELSE 0
       END PW_WORK_TIME,
       TIMESTAMPDIFF(MINUTE, (
       						SELECT PW_EDATE
       						FROM ORDER_WORK
       						WHERE PW_EDATE<= T.PW_SDATE
       						AND PI_NUMB = T.PI_NUMB
       						ORDER BY PW_EDATE DESC
       						LIMIT 0, 1
       						), T.PW_SDATE) PW_WAIT_TIME,
       T.PW_STATE,
       (SELECT CODE_NAME FROM CODE WHERE CODE_ID = 'PW_STATE' AND CODE_VALUE = T.PW_STATE) AS PW_STATE_NAME,
       P.PRS_NAME AS PRS_CODE_NAME,
       E.EQU_NAME,
       U.U_NAME AS PW_USER_NAME,
       C.COMP_NAME AS PW_COMP_NAME,
       TIMESTAMPDIFF(MINUTE, OWS.STOP_SDATE, OWS.STOP_EDATE) STOP_MIN,
       (SELECT CODE_NAME FROM CODE WHERE CODE_ID = 'STOP_NOTE' AND CODE_VALUE = OWS.STOP_NOTE) STOP_NOTE_NAME,
       OWS.REG_DATE
FROM
(
	SELECT O.PI_NUMB,
	       C.COMP_NAME,
	       O.O_DATE,
	       O.D_DATE,
	       O.P_CODE,
		   W.PW_NUMB,
	       W.PRS_NUMB,
	       W.KAI_PRS_NUMB,
	       W.PRS_CODE,
	       W.OUT_MODE,
	       W.EQU_MODE,
	       W.EQU_CODE,
	       W.PW_USER,
	       W.PW_COMP,
	       W.PW_SDATE,
	       W.PW_EDATE,
	       W.PW_STATE
	FROM ORDER_INFO O
	LEFT JOIN COMP_INFO C ON C.COMP_CODE = O.COMP_CODE
	LEFT JOIN ORDER_WORK W ON O.PI_NUMB = W.PI_NUMB 
	WHERE O.O_ENABLE = 'Y'
	AND W.PW_STATE = '03'
	ORDER BY O.PI_NUMB DESC 
) T
LEFT JOIN PROCESS_INFO P ON P.PRS_CODE = T.PRS_CODE
LEFT JOIN EQUIPMENT_INFO E ON E.EQU_CODE = T.EQU_CODE
LEFT JOIN USER_INFO U ON U.U_ID = T.PW_USER
LEFT JOIN COMP_INFO C ON C.COMP_CODE = T.PW_COMP
LEFT JOIN (SELECT * FROM ORDER_WORK_STOP ORDER BY PI_NUMB DESC, PW_NUMB ASC) OWS ON T.PW_NUMB = OWS.PW_NUMB AND T.PI_NUMB = OWS.PI_NUMB
LIMIT 0, 50

 


by 현사또 [2018.07.23 17:52:37]

쿼리진짜 어렵네요

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입