Re: Partitions and performance

[mahesh karipe]

Thanks Rick,

I admire your attention to details...

I will take your suggestions on changing data-type and size. I think we should not take loose decisions while allocating memory.

>> KEY `idx_big` (`YEAR`,`Patient_Id`,`crisk`,`drisk`,`zrisk`,`arisk`,`acrisk`)

This big index was created by myself, expecting the query not to hit the table, instead it should reading all the required data directly from this index.( not sure how much this is usefull !)

> I see no PRIMARY KEY or other UNIQUE key. This seems unlikely.

I realize this now!, the combination of all dimension keys (as this is a fact table) will represent PRIMARY KEY, I will create this now


>> key_buffer_size 57671680
>How much RAM do you have _available_ to MySQL? That number should be about >20%.

I have 8 GB of RAM ( In test server) and 16 GB of RAM in production environment and above number is from test environment, key_buffer_size in production is 2GB.

There is some calculation I find in the net using Key_reads/Key_read_requests = 98% in my case in both test and production environments( not sure on how much sence this equation makes!).


Did not get your point on how much allocated to MYSQL? there is no other thing running on this machine except OS (Windows).


>> lets say if we select dimension A then we have to group the values to the >>respective diemension, same applicable for the rest of the dimension.

>Please provide some concrete examples.

we have to frame the same query which (I have provided for Year/Quarter) using other dimensions, just we have to change the dimension table, few dimension attribuets will change all the measures will be the same and will be aggegated in the same way.


SELECT py.Physician_Id,
py.Speciality Speciality,
py.Physician_Name ,
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 Physician_Id, patient_id -- YEAR,PATIENT_ID in the previous example
) temp INNER JOIN
physician_dim py
ON py.Physician_Id = temp.physician_id
GROUP BY py.Physician_Id



looking forward for your further sugessions.

Thanks
Mahesh