데이터베이스 질문과 답변

mysql subquery 질문 드립니다. 0 12 1,239

by 무정 [MySQL] mysql case then subquery [2022.01.05 10:54:07]

여러개의 테이블을 조인하는 쿼리가 있습니다.

?
1
2
3
4
5
6
7
8
9
10
11
select A.orderno, A.id, A.name, A.hp, B.addr, C.goodname
 
from order A
 
left join member B on A.id=B.id
 
left join goods C on A.goods_num = C.num
 
where settle='결제완료' and (orderday between '1546527600' and '1641394799')
 
group by A.orderno order by A.no desc limit 0,20

이런식으로 결제 목록을 구하는 쿼리 입니다.

아이디값을 이용해 그간 주문횟수를 구하고 싶은데요.

예를 들어 id가 guest 일 경우는 핸드폰번호로 일반 회원일 경우는 아이디로 구하려고 합니다.

저 쿼리안에 넣을 수가 있을까요?

by 마농 [2022.01.05 11:22:37]
?
1
2
3
4
5
6
7
8
SELECT id
     , CASE id WHEN 'guest' THEN hp ELSE name END nm
     , COUNT(*) cnt
  FROM order
 WHERE settle = '결제완료'
 GROUP BY id
     , CASE id WHEN 'guest' THEN hp ELSE name END
;

 

by 무정 [2022.01.05 13:01:33]

아 제가 위에 쿼리문을 정확히 안해놨네요. group by 는 이미 쓰고 있는 상태이고

where 조건문에 날짜값이 들어가 있는 상태입니다.

그리고 제가 구해야 하는 것은 날짜와 상관 없이 주문했던 건수를 구하고 싶은거구요. 

쿼리 속도를 조금이라도 빠르게 하기 위하여 여쭤봅니다. 답변 감사합니다.

by 마농 [2022.01.05 13:05:23]

정확한 정보를 주셔야 정확한 답변을 할 수 있습니다.
쿼리 속도까지 감안한다면?
테이블에 대한 좀 더 많은 정보를 주셔야 합니다.

by 무정 [2022.01.05 13:19:05]

위에 언급했던 order 테이블에 대한 정보입니다. 현재 50만건 정도 되는 주문 테이블입니다.

B.member는 회원의 주소나 레벨을 가져오기 위하여 쓰는 테이블입니다. row수는 약 30만건 됩니다.

C.goods 는 주문한 상품에 대한 정보를 가져오는 테이블입니다. row수는 약 10만건 됩니다.

주문 리스트는 위에 적힌데로 아이디와 상품명과 핸드폰 등등의 정보를 뿌려오구요.

주문한 상품이 2개이상일 경우를 대비해 orderno를 group by 한 상태입니다.

그냥 저 상태로 쿼리를 날리게 되면 0.5초 정도가 걸리는데요.

while안에 아이디별로 주문한 카운트를 구하다 보니 총 0.8초 정도 걸립니다. 그래서 0.3초라도 줄이고자 한 쿼리에 넣으

면 되지 않을까 하여 문의 드렸습니다. 혹시나 더 필요한 정보가 있다면 말씀해주세요. 감사합니다.

by 마농 [2022.01.05 13:22:34]

while 안의 카운트 쿼리도 보여주세요.
인덱스 정보도 알려주세요.
0.8 초가 전체 시간이라면 느린 것 같지는 않은데. 개별 카운트에 0.8초 인가요?
20건이면 0.8초 * 20건 = 16초 걸리는 건가요?

by 무정 [2022.01.05 13:27:29]

비회원일 경우

?
1
select count(*) from order where settle='결제완료' and orderday<='{$row['orderday']}' and hp='{$row['hp']}'

회원 일 경우

?
1
select count(*) from order where settle='결제완료' and orderday<='{$row['orderday']}' and id='{$row['id']}'

인덱스는

order 테이블

pk = no

key = orderno , settle, orderday 이렇게 되어있습니다.

총 시간이 0.8초가 아니라 저 쿼리만 0.8초 입니다. 다른 쿼리까지 합하면 페이지 로딩 완료시까지 2.4초 정도 걸립니다.

다른 쿼리는 페이징을 위하여 총 카운트 내는 쿼리(0.6)와 총 합계금액을 위하여 총주문금액을 구하는 쿼리(0.6)

그리고 담당자 구하는 쿼리(0.4)가 있습니다. 참고로 1년치 정도 검색은 1.2초 정도 걸립니다. 3년 치 검색 시간을 말씀드린겁니다. 감사합니다.

by 마농 [2022.01.05 13:35:09]

orderno 가 pk 가 아니고 no 가 pk 네요?
하나의 주문에 상품이 2개이면? 같은 orderno 에 no 가 2개가 생기는 건가요?
key 에 적어주신 3개 항목은 복합키 1개 인가요? 개별 키 3개 인가요?
카운트 쿼리를 수행하려면? id 인덱스와 hp 인덱스가 별도로 필요합니다.

by 무정 [2022.01.05 13:38:41]

no 는 auto_increment 값입니다.

orderno는 한 주문에 상품이 2개 일 때 2개의 row가 나오기 때문에 group by로 묶어준 부분입니다.

key는 개별키입니다. id값과 hp값에도 인덱스가 필요하단 말씀이시죠? 인덱스를 추가하게 되면 굳이 한 쿼리로 안해도 될까요? 일단 해봐야 겠습니다. 감사합니다.

 

by 마농 [2022.01.05 13:47:10]

네. 인덱스가 필요합니다.
다만. 고민할 사항이 많네요.
테이블 구조로 보면 (orderno 에 대한 중복 가능성)
단순 COUNT(*) 가 맞는가? 고민해야 합니다.
주문은 1번 했는데 상품이 2개라서 2건으로 카운트 될 수 있습니다.
대충 COUNT(*) 로 할지?
정확하게 COUNT(DISTINCT orderno) 로 할지 정해야 합니다.

보통 order 테이블은 orderno 가 키가 되고
order_goods 테이블을 별도로 두는게 좋습니다.
이러면 쿼리가 좀더 간결해 질 수 있습니다.
애초에 테이블 설계에 문제가 있네요.

그리고 메인 쿼리는 조인 후에 페이징 하지 말고
페이징 후에 조인하는게 좋을 듯 하네요.

by 무정 [2022.01.05 14:04:01]

select 를 묶어두고 미리 페이징을 하고 그 다음에 조인을 하란 말씀이신가요?

예를 들어

?
1
2
3
4
5
6
7
select A.orderno, A.id, A.name, A.hp, B.addr, C.goodname from (
  select orderno,id,name,hp from order
  where settle='결제완료' and (orderday between '1546527600' and '1641394799')
  group by A.orderno order by A.no desc limit 0,20
) A
left join member B on A.id=B.id
left join goods C on A.goods_num = C.num

이런식이면 더 빨라질까요?

by 마농 [2022.01.05 14:32:42]

네. 조금은 빨라질 가능성은 있겠지요.
다만. 구조적인 문제 때문에 한계가 있습니다.
테이블 설계가 정규화 되지 않아 불필요한 group by 가 발생되게 되네요.
제대로된 설계였다면? group by 가 필요 없었겠죠.

그리고 goods 와 정확한 조인도 되지 않습니다.
group by 구문이 비표준입니다.
orderno 로 group by 하고 있으므로 원래는 goods_num 은 사용할 수 없습니다.
MySQL 에서만 사용 가능한 구문인데
여러 goods_num 중에서 아무거나 하나만 표현됩니다.
이렇게 아무거나 하나만 표현하는게 맞는건지?
고민이 많아집니다.
ORDER BY no 도 마찬가지입니다.
여러 no 중에서 아무거나 하나만 표현됩니다.
이게 과연 의미가 있는 구문인지?
 

?
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
SELECT a.orderno
     , a.id
     , a.name
     , a.hp
     , a.orderday
     , b.addr
     , CASE WHEN a.id = 'guest'
       THEN (SELECT COUNT(DISTINCT orderno) FROM order WHERE settle = '결재완료' AND orderday <= a.orderday AND hp = a.hp)
       ELSE (SELECT COUNT(DISTINCT orderno) FROM order WHERE settle = '결재완료' AND orderday <= a.orderday AND id = a.id)
        END cnt
  FROM (SELECT orderno
             , id
             , name
             , hp
             , orderday
             , MIN(goods_num) goods_num
          FROM order
         WHERE settle = '결제완료'
           AND orderday BETWEEN '1641049200' AND '1641394799'
         GROUP BY orderno, id, name, hp, orderday
         ORDER BY orderno DESC
         LIMIT 0, 20
        ) a
  LEFT JOIN member b ON a.id = b.id
  LEFT JOIN goods  c ON a.goods_num = c.num
;

 

by 무정 [2022.01.05 14:38:07]

저도 인수인계를 받은 부분인데 페이지가 너무 느리다보니 어떻게든 최적화 해보려고 하는 중입니다.

긴 시간 성실한 답변 해주셔서 감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입