데이터베이스 질문과 답변

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

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초안쪽으로 걸리나 정렬이 틀어집니다..

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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]
?
1
해결했습니다. JOIN 전에 ORDER BY를 먼저하고 하면 해결되네요! 간단한 걸 너무 급해서 올려버렸나 봅니다!
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
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() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입