Re: how to optimise query for 10+ millions records

[Azhar Rahi]

No dear, I did not add any new field in the query. It was the same query which I sent you before. Now I tested it by excluding a field Dur. The current query is

SELECT destination.DestinationId , COUNT(*) TotaCalls , DestinationName -- , SUM(DUR)/60 TotalMinutes
-- ,SUM(IF(Dur > 0,1,0)) TSC -- TSC = Total successful calls
-- ,ROUND((SUM(IF(Dur > 0,1,0)) / COUNT(*)) * 100, 2) ASR -- ASR = Average Success Ratio
-- ,IF(SUM(Dur) > 0, ROUND((SUM(Dur)/60)/SUM(IF(Dur > 0,1,0)), 2),0) ACD

FROM axecdr
FORCE INDEX (indx_DateTimeDestination) -- (indx_StartDateTime, indx_DateTimeDestination, indx_StartDateDestination, indx_AxeCdrStats)

LEFT JOIN destination ON axecdr.DestinationId = destination.DestinationID

WHERE start_date BETWEEN '2012-01-27' AND '2012-02-03'
AND Start_Time BETWEEN '00:00:00' AND '23:59:59' -- AND axecdr.DestinationID = @p_DestinationID
AND IF(@p_CarrierType = 0 AND TRIM(@p_Carriers) <> '', axecdr.IN_ROUTE IN (SELECT `VALUE` FROM IN_ROUTE), axecdr.ID)
AND IF(@p_CarrierType = 1 AND TRIM(@p_Carriers) <> '', axecdr.OUT_ROUTE IN (SELECT `VALUE` FROM OUT_ROUTE), axecdr.ID)
AND IFNULL(destination.DestinationName,'Unknown') LIKE @l_Destination
GROUP BY IFNULL(destination.DestinationName,'Unknown')

When I include the field Dur, it gets slow and takes upto 45-50 seconds but when I exclude it, it takes 11-12 seconds.