데이터베이스 질문과 답변
select * from
PAY_TB_PAYMENT as a
inner join PAY_TB_SELLER_NEW as b
on a.f_code = b.sel_member_id
where 1=1
and (
select count(*) as cnt
from PAY_TB_PAYMENT
where 1=1
and f_code = a.f_code
and card_num = a.card_num
and LEFT(p_date,8) = LEFT(a.p_date,8)
GROUP BY f_code,card_num,LEFT(p_date,8)
) >1 and left(p_date,8) >='20150415' and left(p_date,8) <='20150415' and p_van ='kcp' order by sel_ceo_name,payment_idx desc limit 0, 10
각 테이블의 인덱스 정보와 실행계획 정보를 올려주셔야 할 것 같네요...
쿼리만 가지고 보기에는....
일단 좌측의 기준 컬럼을 가공하는것은 안좋다고 알고있어요
and LEFT(p_date,8) = LEFT(a.p_date,8) 이런 것들을 수정하는게 좋겠네요...
ex) P_DATE BETWEEN LEFT(A.P_DATE,8)||'0000' AND LEFT(A.P_DATE,8)||'2359'
PAY_TB_PAYMENT 테이블 index
"Table" "Non_unique" "Key_name" "Seq_in_index" "Column_name" "Collation" "Cardinality" "Sub_part" "Packed" "Null" "Index_type" "Comment"
"PAY_TB_PAYMENT" "0" "PRIMARY" "1" "payment_idx" "A" "13244" \N \N "" "BTREE" ""
PAY_TB_SELLER_NEW 테이블 index
"Table" "Non_unique" "Key_name" "Seq_in_index" "Column_name" "Collation" "Cardinality" "Sub_part" "Packed" "Null" "Index_type" "Comment"
"PAY_TB_SELLER_NEW" "0" "PRIMARY" "1" "sel_idx" "A" "712" \N \N "" "BTREE" ""
조회페이지 쿼리입니다
select * from PAY_TB_PAYMENT as a
inner join PAY_TB_SELLER_NEW as b
on a.f_code = b.sel_member_id
where EXISTS (select 'X' from PAY_TB_PAYMENT
where f_code = a.f_code
and card_num = a.card_num
and P_DATE BETWEEN LEFT(A.P_DATE,8)||'0000' AND LEFT(A.P_DATE,8)||'2359'
and p_date BETWEEN '201504150000' AND '201504152359'
and p_van ='kcp'
order by sel_ceo_name, payment_idx desc limit 0, 10
) 버튼을 클릭하여 작성 하시면 됩니다.