SELECT a.pv, b.view, c.click, (b.view+c.click)-d.overlap FROM
(SELECT COUNT(distinct vid) pv FROM user_pv_12566 where cururl=1 AND corpid=1405 AND secid=2646 AND adid=147394 AND
rptday<=20091116) a,
(SELECT COUNT(distinct vid) view FROM user_view_12566 where cururl=1 AND corpid=1405 AND secid=2646 AND adid=147394 AND
rptday<=20091116) b,
(SELECT COUNT(distinct vid) click FROM user_click_12566 where cururl=1 AND corpid=1405 AND secid=2646 AND adid=147394 AND
rptday<=20091116) c,
(
SELECT COUNT(DISTINCT x.vid) overlap FROM
user_view_12566 x, user_click_12566 y
WHERE
x.cururl=26 and y.cururl=26 and
x.corpid=1405 and y.corpid=1405 and
x.secid=2646 and y.secid=2646 and
x.adid=147394 and y.adid=147394 and
x.rptday<=20091116 and y.rptday<=20091116 and
x.vid = y.vid) d
이런식으로 3개의 테이블을 사용하는데요 3개다 테이블 구조는 같습니다.
where 걸려있는 칼럼들은 모두 index 걸려있구요
corpid=1405 AND secid=2646 AND adid=147394 AND rptday
이 칼럼을 자주 where 거는데
인덱스를 싸그리 묶어서 걸어주면더 빠를까요?
아님
x.cururl=26 and y.cururl=26 and
x.corpid=1405 and y.corpid=1405 and
x.secid=2646 and y.secid=2646 and
x.adid=147394 and y.adid=147394 and
x.rptday<=20091116 and y.rptday<=20091116 and
x.vid = y.vid
이 부분때문에 느려지는거 같기도 한데 이걸 좀 바꿀방법이 없나요?
|