MySQL Forums
Forum List  »  Optimizer & Parser

Re: how to optimise query for 10+ millions records
Posted by: Azhar Rahi
Date: February 12, 2012 10:12PM

@ 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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: how to optimise query for 10+ millions records
1508
February 12, 2012 10:12PM


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.