Partitions and performance

[mahesh karipe]

Hi,

We have OLAP application and we are using MYISAM engine. We have a huge fact table (6000 K records), to improve the performance we have created an aggregated fact table but still the row count 2000K. When we do some aggregation (for sum/count) on this aggregated table, taking nearly one minute response time, this is not acceptable to the client (need this in less than 20 sec or less).

I tried with paritioning, but did not see any difference, as we need to run the query on the whole data.

sample query:

SELECT YEAR,
SUM(patCnt) PatientCount,
ROUND(AVG(CRisk), 2) CRisk,
ROUND(AVG(DRisk), 2) DRisk,
ROUND(AVG(ZRisk), 2) ZRisk,
ROUND(AVG(ARisk), 2) ARisk,
AVG(ACRisk) ACRisk
FROM (
SELECT YEAR,
ROUND(AVG(CRisk), 2) CRisk,
ROUND(AVG(DRisk), 2) DRisk,
ROUND(AVG(ZRisk), 2) ZRisk,
ROUND(AVG(ARisk), 2) ARisk,
AVG(ACRisk) ACRisk
FROM tmp_qfact_p2 ps
GROUP BY YEAR, patient_id
) temp
GROUP BY YEAR;




Please assist to imporve the same.

Thanks
Mahesh