I have to similar queries which the only difference is that one is doing a sum of a column and the other is doing a count(distinct) of another column. The first one runs in seconds (17s) and the other one never stops (1 hour and counting). I've seen the plan for the count query and it has huge costs. I don't understand why. They are hitting the exact same views. Why is this happening and what can I do?
The one that is running fine:
select a11.SOURCEPP SOURCEPP,
a12.DUMMY DUMMY,
a11.SIM_NAME SIM_NAME,
a13.THEORETICAL THEORETICAL,
sum(a11.REVENUE) WJXBFS1
from CLIENT_SOURCE_DATA a11
join DUMMY_V a12
on (a11.SOURCEPP = a12.SOURCEPP)
join SIM_INFO a13
on (a11.SIM_NAME = a13.SIM_NAME)
where (a13.THEORETICAL in (0)
and a11.SIM_NAME in ('ETS40'))
group by a11.SOURCEPP,
a12.DUMMY,
a11.SIM_NAME,
a13.THEORETICAL
the one that doesn't run:
select a12.SOURCEPP SOURCEPP,
a12.SIM_NAME SIM_NAME,
a13.THEORETICAL THEORETICAL,
count(distinct a12.CLIENTID) WJXBFS1
from CLIENT_SOURCE_DATA a12
join SIM_INFO a13
on (a12.SIM_NAME = a13.SIM_NAME)
where (a13.THEORETICAL in (0)
and a12.SIM_NAME in ('ETS40'))
group by a12.SOURCEPP,
a12.SIM_NAME,
a13.THEORETICAL
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `CLIENT_SOURCE_DATA` ADD INDEX `client_data_idx_sim_name` (`SIM_NAME`);
ALTER TABLE `DUMMY_V` ADD INDEX `dummy_v_idx_sourcepp` (`SOURCEPP`);
ALTER TABLE `SIM_INFO` ADD INDEX `sim_info_idx_sim_name_theoretical` (`SIM_NAME`,`THEORETICAL`);
SELECT
a11.SOURCEPP SOURCEPP,
a12.DUMMY DUMMY,
a11.SIM_NAME SIM_NAME,
a13.THEORETICAL THEORETICAL,
sum(a11.REVENUE) WJXBFS1
FROM
CLIENT_SOURCE_DATA a11
JOIN
DUMMY_V a12
ON (
a11.SOURCEPP = a12.SOURCEPP
)
JOIN
SIM_INFO a13
ON (
a11.SIM_NAME = a13.SIM_NAME
)
WHERE
(
a13.THEORETICAL IN (
0
)
AND a11.SIM_NAME IN (
'ETS40'
)
)
GROUP BY
a11.SOURCEPP,
a12.DUMMY,
a11.SIM_NAME,
a13.THEORETICAL
ORDER BY
NULL