[MySQL] 멀티 컬럼 select

CREATE TABLE `mytable` (
  `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번이 가장 나아보임.