'여러 컬럼'에 해당되는 글 1건
[MySQL] 멀티 컬럼 select[MySQL] 멀티 컬럼 select
Posted at 2014. 3. 12. 11:03 | Posted in Server`userid` bigint(20) NOT NULL,
`groupid` bigint(20) NOT NULL,
`state` tinyint(1) NOT NULL,
`col1` bigint(20) DEFAULT NULL,
`cal2` varchar(200) NOT NULL,
`cal3` varchar(100) NOT NULL,
`create_time` datetime NOT NULL,
`update_time` datetime NOT NULL,
PRIMARY KEY (`userid`,`groupid`),
KEY `idx_userid_createtime` (`userid`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
(1) 컬럼 조합 IN 쿼리
EXPLAIN
SELECT *
FROM mytable
WHERE (userId, groupid) IN ((1526291770280964803,2679212559264916326),(1526292017760867305, 2679249801360511502),(1526421185616035771,2679342905646882845));
id|select_type|table |type|possible_keys|key |key_len|ref |rows|Extra
1 |SIMPLE |mytable|ALL |NULL |NULL|NULL |NULL|172 |Using where
index를 타지 않는다.
(2) FROM절 subquery
EXPLAIN
SELECT a.* FROM (SELECT * FROM mytable WHERE userid IN (1526291770280964803,1526292017760867305,1526421185616035771)) a
WHERE a.groupid IN (2679212559264916326, 2679249801360511502, 2679342905646882845);
id|select_type|table |type |possible_keys|key|key_len|ref|rows|Extra
1 |PRIMARY |<derived2>|ALL |NULL|NULL|NULL|NULL|15|Using where
2 |DERIVED |mytable |range|PRIMARY,idx_userid_createtime|PRIMARY|8|NULL|15|Using where
userid가 index를 탄다. groupid도 인덱스를 걸어줘야 할 듯.
(3) 각 컬럼에 대해서 IN query
EXPLAIN
SELECT *
FROM mytable WHERE userid IN (1526291770280964803,1526292017760867305,1526421185616035771) AND groupid IN (2679212559264916326, 2679249801360511502, 2679342905646882845);
id|select_type|table |type |possible_keys|key|key_len|ref|rows|Extra
1 |SIMPLE |mytable|range|PRIMARY,idx_userid_createtime|PRIMARY|16|NULL|9|Using where
결론) 3번이 가장 나아보임.
www.deepblue.pe.kr
