MySQL Forums
Forum List  »  Optimizer & Parser

Re: how to optimise query for 10+ millions records
Posted by: Azhar Rahi
Date: February 20, 2012 02:21AM

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.

Options: ReplyQuote

Written By
Re: how to optimise query for 10+ millions records
February 20, 2012 02:21AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.