Mysql Union 쿼리 최적화 방법
본문
안녕하세요.
DB 자료 조회 중 원하는 내용을 뽑아내기 위해 임시로 Union을 사용 했는데 너무 비효율적인 방법이라,
해당 쿼리를 최적화 할 수 있는 방법이 있을지 알고 싶습니다.
[쿼리 전문]
SELECT ID, COUNT(ID) FROM(
SELECT itemid01 AS ID FROM item_collection WHERE collectionid IN (0,1,2,3,4,5,6,7,8,9)
UNION ALL
SELECT itemid02 AS ID FROM item_collection WHERE collectionid IN (0,1,2,3,4,5,6,7,8,9)
UNION ALL
SELECT itemid03 AS ID FROM item_collection WHERE collectionid IN (0,1,2,3,4,5,6,7,8,9)
UNION ALL
SELECT itemid04 AS ID FROM item_collection WHERE collectionid IN (0,1,2,3,4,5,6,7,8,9)
UNION ALL
SELECT itemid05 AS ID FROM item_collection WHERE collectionid IN (0,1,2,3,4,5,6,7,8,9)
UNION ALL
SELECT itemid06 AS ID FROM item_collection WHERE collectionid IN (0,1,2,3,4,5,6,7,8,9)
UNION ALL
SELECT itemid07 AS ID FROM item_collection WHERE collectionid IN (0,1,2,3,4,5,6,7,8,9)
UNION ALL
SELECT itemid08 AS ID FROM item_collection WHERE collectionid IN (0,1,2,3,4,5,6,7,8,9)
) a WHERE ID != 0 GROUP BY ID;
item_Collection 테이블에 collectionid 마다 해당 콜렉션에 필요한 재료가 8개씩 개별 컬럼(itemid01~08)으로 들어 있습니다.
[예시]
id itemid01 itemid02 itemid03 itemid04 itemid05 itemid06 itemid07 itemid08
0 a b c d
1 a c d e f
2 b d f g
현재 필요한 내용은 collectionid가 [0,1,2,3,4,5,6,7,8,9]인 항목을 모두 완료하기 위한 필요재료를 모두 뽑아 내고자 합니다.
위의 쿼리를 실행하면
[예시]
id count(id)
a 10
b 5
c 7
d 6
e 1
f 2
g 4
위와 같은 형식으로 종류와 갯수를 카운트가 출력됩니다.
원하는 결과값은 얻었지만 해당 쿼리문이 최적의 쿼리문은 아니라 생각되어, 더 최적화 할 수 있는 방법이 있는지 노하우가 있다면 말씀 부탁 드리겠습니다.
답변 2
단계를 다르게 생각해보시는게 나을것 같습니다.
item_collection
여기에 collectionid 가 어떤 값들이 있는지는 모르지만
필요한것은
in (0,1,2,3,4,5,6,7,8,9)
로 보여지네요
그러면
가져올 컬럼들의 전체와 where절의 전체를 하나의 테이블로 만듭니다.
create table tmp_item_collection as
SELECT
itemid01, itemid02, itemid03, itemid04, itemid05, itemid06, itemid07, itemid08,
FROM item_collection WHERE collectionid IN (0,1,2,3,4,5,6,7,8,9)
;
그리고나서 한번더 임시테이블로 만듭니다.
create table tmp_item_ids as
select itemid01 as id from tmp_item_collection
union all
select itemid02 as id from tmp_item_collection
union all
select itemid03 as id from tmp_item_collection
union all
select itemid04 as id from tmp_item_collection
union all
select itemid05 as id from tmp_item_collection
union all
select itemid06 as id from tmp_item_collection
union all
select itemid07 as id from tmp_item_collection
union all
select itemid08 as id from tmp_item_collection
;
마지막으로 수행하고자 하는 기능을 처리합니다.
select id, count(id) cnt from tmp_item_ids
group by id
;
위 내용이 반복적으로 된다면
create table 앞에
drop table을 추가하는것도 방법입니다.
임시 테이블 만드는 방법 검토 해보도록 하겠습니다. 감사합니다 : )
DB 모델링을 처음부터
M:N으로 하셨으면 좋았을 듯합니다.
다른 사람이 구성해둔 DB이고 이미 서비스 되고 있는 부분이라 리팩토링이 쉽지 않네요 ㅠㅠ
item ---> item_collection <---- part
item_collection은 (collectionid , part_no) 이렇게 구성하면
query문이 아주 간결해 질 듯합니다.