안녕하세요. 이번에 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