Re: how to optimise query for 10+ millions records

[Azhar Rahi]

@ IIrek .. Destination Table is right table i.e. on the right of left join. However I can avoid IFNULL(destination.DestinationName,'Unknown') LIKE @dest if necessary.

Well Here is the structure of the left table:

CREATE TABLE `axecdr` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`NCR` varchar(100) DEFAULT NULL,
`Call_ID` varchar(20) DEFAULT NULL,
`START_DATE` date DEFAULT NULL,
`START_TIME` time DEFAULT NULL,
`DUR` int(11) DEFAULT NULL,
`A_NUMMER` int(11) DEFAULT NULL,
`USR_CALLING_NUM` varchar(20) DEFAULT NULL,
`B_NUMMER` varchar(100) DEFAULT NULL,
`CONTR_CALLED_NUM` varchar(100) DEFAULT NULL,
`IN_ROUTE` varchar(100) DEFAULT NULL,
`OUT_ROUTE` varchar(100) DEFAULT NULL,
`ATTEMPT_CAUSE_CODE` int(11) DEFAULT NULL,
`ATTEMPT_LOCATION_CODE` int(11) DEFAULT NULL,
`CARRIER_ACCESS_CODE` int(11) DEFAULT NULL,
`DestinationID` int(11) DEFAULT NULL,
`NCR2` varchar(100) DEFAULT NULL,
`CALL_ID2` varchar(20) DEFAULT NULL,
`START_DATE2` date DEFAULT NULL,
`START_TIME2` time DEFAULT NULL,
`DUR2` int(11) DEFAULT NULL,
`A_NUMMER2` int(11) DEFAULT NULL,
`USR_CALLING_NUM2` varchar(20) DEFAULT NULL,
`B_NUMMER2` varchar(100) DEFAULT NULL,
`CONTR_CALLED_NUM2` varchar(100) DEFAULT NULL,
`IN_ROUTE2` varchar(100) DEFAULT NULL,
`OUT_ROUTE2` varchar(100) DEFAULT NULL,
`ATTEMPT_CAUSE_CODE2` int(11) DEFAULT NULL,
`ATTEMPT_LOCATION_CODE2` int(11) DEFAULT NULL,
`CARRIER_ACCESS_CODE2` int(11) DEFAULT NULL,
`NCR3` varchar(100) DEFAULT NULL,
`CALL_ID3` varchar(20) DEFAULT NULL,
`START_DATE3` date DEFAULT NULL,
`START_TIME3` time DEFAULT NULL,
`DUR3` int(11) DEFAULT NULL,
`A_NUMMER3` int(11) DEFAULT NULL,
`USR_CALLING_NUM3` varchar(100) DEFAULT NULL,
`B_NUMMER3` varchar(100) DEFAULT NULL,
`CONTR_CALLED_NUM3` varchar(100) DEFAULT NULL,
`IN_ROUTE3` varchar(100) DEFAULT NULL,
`OUT_ROUTE3` varchar(100) DEFAULT NULL,
`ATTEMPT_CAUSE_CODE3` int(11) DEFAULT NULL,
`ATTEMPT_LOCATION_CODE3` int(11) DEFAULT NULL,
`CARRIER_ACCESS_CODE3` int(11) DEFAULT NULL,
`CDRSourceid` tinyint(4) DEFAULT NULL,
`Filename` varchar(250) DEFAULT NULL,
`SourceType` tinyint(4) DEFAULT NULL,
`Status` tinyint(4) DEFAULT NULL,
`ProcessDate` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `NewIndex1` (`NCR`,`Call_ID`,`START_DATE`,`START_TIME`,`B_NUMMER`),
KEY `indx_destinationid` (`DestinationID`),
KEY `indx_status` (`Status`),
KEY `indx_StartDateDestination` (`START_DATE`,`DestinationID`),
KEY `indx_StartDateTime` (`START_DATE`,`START_TIME`),
KEY `indx_DateTimeDestination` (`START_DATE`,`START_TIME`,`DestinationID`),
KEY `indx_AxeCdrStats` (`START_DATE`,`START_TIME`,`IN_ROUTE`,`OUT_ROUTE`,`DestinationID`),
ENGINE=InnoDB AUTO_INCREMENT=5266710 DEFAULT CHARSET=latin1

Here is the structure of right table:

CREATE TABLE `destination` (
`DestinationID` int(11) NOT NULL AUTO_INCREMENT,
`DestinationName` varchar(255) NOT NULL,
`CountryID` int(11) DEFAULT '0',
`IsTopDestination` tinyint(4) NOT NULL DEFAULT '0',
`SortOrder` smallint(6) NOT NULL DEFAULT '0',
`DestinationCategoryID` smallint(6) DEFAULT NULL,
PRIMARY KEY (`DestinationID`),
UNIQUE KEY `IDX_Destination` (`DestinationName`),
KEY `FK_destination` (`CountryID`),
KEY `index1` (`DestinationCategoryID`)
) ENGINE=InnoDB AUTO_INCREMENT=12508 DEFAULT CHARSET=latin1

My Comp Ram is 2 GB:

Here is the query:

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

FROM axecdr
FORCE INDEX (indx_StartDateTime, indx_StartDateDestination, indx_AxeCdrStats, indx_AxeCdrStats2, indx_DateTimeDestination, indx_DestinationStartDate)
LEFT JOIN destination ON axecdr.DestinationId = destination.DestinationID

WHERE start_date BETWEEN '2012-01-27' AND '2012-01-28'
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 destination.DestinationName LIKE '%%' -- @l_Destination
AND IFNULL(destination.DestinationName,'Unknown') LIKE @l_Destination
GROUP BY axecdr.START_DATE, axecdr.DestinationID

In this query, the query is getting strange. When I give date range 2012-01-27 to 2012-01-28, and then checn the analysis of query with Explain keyword, it shows that query type is 'range' , ref is null and extras is "using where" only, but when I give both dates to be the same i.e. 2012-01-27 to 2012-01-27, its type turns remains range, ref is null but extras are "Using where; Using temporary; Using filesort". And same in the case when I give 2012-01-27 and 2012-01-29.

I am unable to resolve the problem. I need it urgent because my site is live now and it has made it slow. I am very nervous where the problem is.