레이블이 Multi-Column IN인 게시물을 표시합니다. 모든 게시물 표시
레이블이 Multi-Column IN인 게시물을 표시합니다. 모든 게시물 표시

Multi-Column IN clause – Unexpected MySQL Issue

Multi-Column IN 절을 사용하는 SQL이라면, 인덱스를 제대로 활용하지 못하고 Full table scan을 유발할 수 있다.
다음의 테스트 시나리오를 살펴보자.TAB_SUMM 테이블에는 314410개의 행이 존재한다.
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
--==============================================================================
-- 010 Version
--==============================================================================
mysql > select @@VERSION;
+--------------------+
| @@VERSION          |
+--------------------+
| 5.5.37-MariaDB-log |
+--------------------+
--==============================================================================
-- 020 Shows the CREATE TABLE statement that creates the named table
--==============================================================================
CREATE TABLE TAB_SUMM
(
    TYPE_SEQ        bigint(20)    NOT NULL              COMMENT '유형일련번호'
  , TYPE_SUB_SEQ    varchar(100)  NOT NULL              COMMENT '유형보조일련번호'
  , TOTAL_CNT       int(11)       NOT NULL  DEFAULT '0' COMMENT '전체개수'
  , ATACH_CNT       int(11)       NOT NULL  DEFAULT '0' COMMENT '첨부개수'
  , REG_DATE        datetime      NOT NULL              COMMENT '등록일시'
  , UPDT_DATE       datetime      NOT NULL              COMMENT '수정일시'
  , PRIMARY KEY (TYPE_SEQ, TYPE_SUB_SEQ)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='합계'
--==============================================================================
-- 030 data distribution
--==============================================================================
mysql > SELECT COUNT(*) FROM TAB_SUMM;
+----------+
| COUNT(*) |
+----------+
|   314410 |
+----------+
mysql > SELECT COUNT(*) FROM
        (
          SELECT TYPE_SEQ 
          FROM TAB_SUMM 
          GROUP BY TYPE_SEQ
        ) TAB;
+----------+
| COUNT(*) |
+----------+
|       34 |
+----------+
cs
SELECT 쿼리의 WHERE 조건에 Multi_column IN( (TYPE_SEQ, TYPE_SUB_SEQ ) IN (....) )을 사용하면 Primary Key를 활용하지 못하고 Full Table Scan이 발생한다.
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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
--==============================================================================
-- 040 Query - AS-IS
--==============================================================================
mysql > SELECT
            TYPE_SEQ
          , TYPE_SUB_SEQ
          , TOTAL_CNT
          , ATACH_CNT
        FROM TAB_SUMM 
        WHERE (TYPE_SEQ, TYPE_SUB_SEQ) in 
        (
            (104, '51122581')
          , (104, '51122568')
          , (104, '50146542')
          , (102, '3211252')
          , (104, '51122567')
          , (104, '51122566')
          , (102, '3211253')
          , (102, '3211251')
          , (104, '51122564')
          , (102, '848653')
          , (102, '1324159')
          , (102, '1339256')
        );
-- RESULT
+----------+--------------+-----------+-----------+
| TYPE_SEQ | TYPE_SUB_SEQ | TOTAL_CNT | ATACH_CNT |
+----------+--------------+-----------+-----------+
|      104 | 50146542     |         3 |         3 |
+----------+--------------+-----------+-----------+
1 row in set (0.12 sec)
-- EXCUTION PLAN
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | PCCS  | ALL  | NULL          | NULL | NULL    | NULL | 294042 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
-- HANDLER USAGE (FLUSH STATUS;SHOW STATUS LIKE '%HANDLER%';)
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 1      |
| Handler_delete             | 0      |
| Handler_discover           | 0      |
| Handler_icp_attempts       | 0      |
| Handler_icp_match          | 0      |
| Handler_mrr_init           | 0      |
| Handler_mrr_key_refills    | 0      |
| Handler_mrr_rowid_refills  | 0      |
| Handler_prepare            | 0      |
| Handler_read_first         | 0      |
| Handler_read_key           | 0      |
| Handler_read_last          | 0      |
| Handler_read_next          | 0      |
| Handler_read_prev          | 0      |
| Handler_read_rnd           | 0      |
| Handler_read_rnd_deleted   | 0      |
| Handler_read_rnd_next      | 314412 |
| Handler_rollback           | 0      |
| Handler_savepoint          | 0      |
| Handler_savepoint_rollback | 0      |
| Handler_tmp_update         | 0      |
| Handler_tmp_write          | 0      |
| Handler_update             | 0      |
| Handler_write              | 0      |
+----------------------------+--------+
-- PROFILE
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| QUERY_ID | SEQ | STATE                | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION       | SOURCE_FILE   | SOURCE_LINE |
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
|        2 |   2 | starting             | 0.000104 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                  | NULL          |        NULL |
|        2 |   3 | checking permissions | 0.000010 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_parse.cc  |        4914 |
|        2 |   4 | Opening tables       | 0.000048 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc   |        4981 |
|        2 |   5 | System lock          | 0.000007 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc       |         307 |
|        2 |   6 | Table lock           | 0.000011 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc       |         312 |
|        2 |   7 | init                 | 0.000045 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 1 |     0 | mysql_select          | sql_select.cc |        3063 |
|        2 |   8 | optimizing           | 0.000026 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_select.cc |         983 |
|        2 |   9 | statistics           | 0.000027 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_select.cc |        1225 |
|        2 |  10 | preparing            | 0.000025 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_select.cc |        1250 |
|        2 |  11 | executing            | 0.000006 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_select.cc |        2231 |
|        2 |  12 | Sending data         | 0.120535 | 0.122981 |   0.000999 |                52 |                   0 |           16 |           112 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_select.cc |        2862 |
|        2 |  13 | end                  | 0.000022 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_select          | sql_select.cc |        3098 |
|        2 |  14 | query end            | 0.000009 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc  |        4522 |
|        2 |  15 | closing tables       | 0.000013 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc  |        4582 |
|        2 |  16 | freeing items        | 0.000016 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc  |        5813 |
|        2 |  17 | updating status      | 0.000016 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc  |        1469 |
|        2 |  18 | cleaning up          | 0.000017 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc  |        1486 |
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
+----------------------+----------+-------+-------+--------------+
| STATE                | Total_R  | Pct_R | Calls | R/Call       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 0.120535 | 99.67 |     1 | 0.1205350000 |
| starting             | 0.000104 |  0.09 |     1 | 0.0001040000 |
| Opening tables       | 0.000048 |  0.04 |     1 | 0.0000480000 |
| init                 | 0.000045 |  0.04 |     1 | 0.0000450000 |
| statistics           | 0.000027 |  0.02 |     1 | 0.0000270000 |
| optimizing           | 0.000026 |  0.02 |     1 | 0.0000260000 |
| preparing            | 0.000025 |  0.02 |     1 | 0.0000250000 |
| end                  | 0.000022 |  0.02 |     1 | 0.0000220000 |
| cleaning up          | 0.000017 |  0.01 |     1 | 0.0000170000 |
| updating status      | 0.000016 |  0.01 |     1 | 0.0000160000 |
| freeing items        | 0.000016 |  0.01 |     1 | 0.0000160000 |
| closing tables       | 0.000013 |  0.01 |     1 | 0.0000130000 |
| Table lock           | 0.000011 |  0.01 |     1 | 0.0000110000 |
| checking permissions | 0.000010 |  0.01 |     1 | 0.0000100000 |
| query end            | 0.000009 |  0.01 |     1 | 0.0000090000 |
| System lock          | 0.000007 |  0.01 |     1 | 0.0000070000 |
| executing            | 0.000006 |  0.00 |     1 | 0.0000060000 |
+----------------------+----------+-------+-------+--------------+
cs

Multi-column IN절을 사용할 때, Primary Key 등의 인덱스를 활용하지 못하는 것은 Query Optimizing에서의 버그로 보이며,  MySQL 5.5, 5.6, MariaDB 5.5, MariaDB 10.0.X 에서도 동일하게 발생한다.
해당 버그는 MySQL 5.7.3에서 에서 Fix되었다고 한다.
MySQL 5.5 또는 MariaDB 5.5 버전을 사용중이라면, 해당 버그로 MySQL의 버전을 5.7.3 이상으로 당장 업데이트하기 부담스러울 수 있다.
따라서 Multi-column IN절을 사용하는 쿼리를 다음과 같이 Inner join으로 간단히 변경하는 것을 검토해보길 바란다.
Inner join으로 변경하고 SELECT 쿼리 수행하면ㅡ Primary Key를 정상적으로 활용하고 Full table scan이 아닌 Index range scan으로 데이터를 읽는다. 당연히 쿼리 응답속도도 크게 감소하였다.

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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
--==============================================================================
-- 050 Query - Tuning
--==============================================================================
mysql > SELECT
            TYPE_SEQ
          , TYPE_SUB_SEQ
          , TOTAL_CNT
          , ATACH_CNT
        FROM TAB_SUMM 
        WHERE 1=1
          AND (
                     (TYPE_SEQ=104 AND TYPE_SUB_SEQ IN ('51122581','51122568','50146542','51122567','51122566','51122564'))
                  OR (TYPE_SEQ=102 AND TYPE_SUB_SEQ IN ('3211252','3211253','3211251','848653','1324159','1339256'))
              );
      
-- RESULT
+----------+--------------+-----------+-----------+
| TYPE_SEQ | TYPE_SUB_SEQ | TOTAL_CNT | ATACH_CNT |
+----------+--------------+-----------+-----------+
|      104 | 50146542     |         3 |         3 |
+----------+--------------+-----------+-----------+
1 row in set (0.00 sec)
-- EXCUTION PLAN
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | PCCS  | range | PRIMARY       | PRIMARY | 310     | NULL |   12 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
-- HANDLER USAGE(FLUSH STATUS;SHOW STATUS LIKE '%HANDLER%';)
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 12    |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
-- PROFILE
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| QUERY_ID | SEQ | STATE                | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION       | SOURCE_FILE   | SOURCE_LINE |
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
|        4 |   2 | starting             | 0.000084 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                  | NULL          |        NULL |
|        4 |   3 | checking permissions | 0.000008 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_parse.cc  |        4914 |
|        4 |   4 | Opening tables       | 0.000016 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc   |        4981 |
|        4 |   5 | System lock          | 0.000004 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc       |         307 |
|        4 |   6 | Table lock           | 0.000009 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc       |         312 |
|        4 |   7 | init                 | 0.000033 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_select          | sql_select.cc |        3063 |
|        4 |   8 | optimizing           | 0.000030 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_select.cc |         983 |
|        4 |   9 | statistics           | 0.000072 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_select.cc |        1225 |
|        4 |  10 | preparing            | 0.000026 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_select.cc |        1250 |
|        4 |  11 | executing            | 0.000006 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_select.cc |        2231 |
|        4 |  12 | Sending data         | 0.000087 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 1 |     0 | exec                  | sql_select.cc |        2862 |
|        4 |  13 | end                  | 0.000012 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_select          | sql_select.cc |        3098 |
|        4 |  14 | query end            | 0.000005 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc  |        4522 |
|        4 |  15 | closing tables       | 0.000008 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc  |        4582 |
|        4 |  16 | freeing items        | 0.000013 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc  |        5813 |
|        4 |  17 | updating status      | 0.000012 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc  |        1469 |
|        4 |  18 | cleaning up          | 0.000019 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc  |        1486 |
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
+----------------------+----------+-------+-------+--------------+
| STATE                | Total_R  | Pct_R | Calls | R/Call       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 0.000087 | 19.59 |     1 | 0.0000870000 |
| starting             | 0.000084 | 18.92 |     1 | 0.0000840000 |
| statistics           | 0.000072 | 16.22 |     1 | 0.0000720000 |
| init                 | 0.000033 |  7.43 |     1 | 0.0000330000 |
| optimizing           | 0.000030 |  6.76 |     1 | 0.0000300000 |
| preparing            | 0.000026 |  5.86 |     1 | 0.0000260000 |
| cleaning up          | 0.000019 |  4.28 |     1 | 0.0000190000 |
| Opening tables       | 0.000016 |  3.60 |     1 | 0.0000160000 |
| freeing items        | 0.000013 |  2.93 |     1 | 0.0000130000 |
| updating status      | 0.000012 |  2.70 |     1 | 0.0000120000 |
| end                  | 0.000012 |  2.70 |     1 | 0.0000120000 |
| Table lock           | 0.000009 |  2.03 |     1 | 0.0000090000 |
| closing tables       | 0.000008 |  1.80 |     1 | 0.0000080000 |
| checking permissions | 0.000008 |  1.80 |     1 | 0.0000080000 |
| executing            | 0.000006 |  1.35 |     1 | 0.0000060000 |
| query end            | 0.000005 |  1.13 |     1 | 0.0000050000 |
| System lock          | 0.000004 |  0.90 |     1 | 0.0000040000 |
+----------------------+----------+-------+-------+--------------+
cs

아울러
MySQL 쿼리 개발 시, Inner JOIN으로 작성해야 하는 쿼리를 개발 편의성을 위하여 습관적으로 Multi-column IN을 사용하도록 작성한다면 쿼리 수행 성능이 좋지 않아 큰 낭패를 볼 수 있다. 특히 ORM을 사용하는 환경이라면 그 발생 빈도가 클 수 있다.
MySQL 5.7.3 미만의 버전이라면 Multi-column IN 사용을 최대한 지양하고 JOIN 방법을 사용하길 바란다.
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
--==============================================================================
-- Multi-column IN Clause
--   : 0.01 sec (maximum 0.4 sec)
--==============================================================================
SELECT ...........
FROM TEST_TABLE_A
WHERE (COLA, COLB) IN (
                          SELECT COLA, MAX(COLB) 
                          FROM TEST_TABLE_B
                          WHERE COLC = 'AAAAAAA'
                          GROUP BY COLA
                      );
-- EXCUTION PLAN
+----+-------------+--------------+------+--------------------+-------------------+---------+-------+------+----------------------------------------------+
| id | select_type | table        | type | possible_keys      | key               | key_len | ref   | rows | Extra                                        |
+----+-------------+--------------+------+--------------------+-------------------+---------+-------+------+----------------------------------------------+
|  1 | PRIMARY     | TEST_TABLE_A | ALL  | NULL               | NULL              | NULL    | NULL  | 5319 | Using where                                  |
|  2 | SUBQUERY    | TEST_TABLE_B | ref  | TEST_TABLE_B_IDX1  | TEST_TABLE_B_IDX1 | 8       | const |   19 | Using where; Using temporary; Using filesort |
+----+-------------+--------------+------+--------------------+-------------------+---------+-------+------+----------------------------------------------+
--==============================================================================
-- Multi-column IN Clause --> Inner Join
--   : 0.00 sec (maximum 0.01 sec)
--==============================================================================                      
SELECT ...........
FROM TEST_TABLE_A TTA
          INNER JOIN (
                          SELECT COLA, MAX(COLB) as MAX_COLB
                          FROM TEST_TABLE_B
                          WHERE COLC = 'AAAAAAA'
                          GROUP BY COLA
                      ) TTB ON TTA.COLA = TTB.COLA AND TTA.COLB = TTB.MAX_COLB;
-- EXCUTION PLAN
+----+-------------+---------------------+--------+----------------------+-------------------+---------+----------------+------+----------------------------------------------+
| id | select_type | table               | type   | possible_keys        | key               | key_len | ref            | rows | Extra                                        |
+----+-------------+---------------------+--------+----------------------+-------------------+---------+----------------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>          | ALL    | NULL                 | NULL              | NULL    | NULL           |   19 | Using where                                  |
|  1 | PRIMARY     | TEST_TABLE_A        | eq_ref | PRIMARY              | PRIMARY           | 8       | TTB.MAX_COLB   |    1 | Using where                                  |
|  2 | SUBQUERY    | TEST_TABLE_B        | ref    | TEST_TABLE_B_IDX1    | TEST_TABLE_B_IDX1 | 8       | const          |   19 | Using where; Using temporary; Using filesort |
+----+-------------+---------------------+--------+----------------------+-------------------+---------+----------------+------+----------------------------------------------+
cs

작성자: 시간: 댓글 없음:
라벨: ,
피드 구독하기: 글 (Atom)