CODEWITHSUNDEEP
mysqlsqloptimizationquery-optimization
alex
alex

Reputation: 1350

MySql: Optimizing a query

Trying to optimize the following query as it runs very slow. I've done some optimizations on it but sill runs very slow > 30 seconds.

Here's the query:

set @_YEAR = 2014;
set @_MONTH = 1;
set @_PRODUCTS = '53';

set @_START = date(@_YEAR * 10000 + 1 * 100 + 1);  
set @_END = date_add(date(@_YEAR * 10000 + 1 * 100 + 1), interval 1 year);

explain SELECT TG, TS, `MONTH` 

FROM (

    SELECT SG.company, SUM(SG.Gain) as TG, SUM(SG.Spill) as TS, SG.`MONTH`

    FROM (

        (SELECT c.called as customer, c.calling as company, MONTH(c.`end`) as 'MONTH',
                @G := if(@prevComp <> c.calling AND @prevCust = c.called AND @prevDate = DATE_FORMAT(c.`end`,'%Y-%m-%d'), 1, 0) as Gain,
                @S := 0 as Spill,
                @prevCust := c.called as prevCust,
                @prevComp := c.calling as prevComp,
                @prevDate := DATE_FORMAT(c.`end`,'%Y-%m-%d') as prevDate
        FROM cdrdata_archive c CROSS JOIN
             (SELECT @prevComp := 0, @prevCust := 0, @prevDate := 0) prevVals
        where c.`end` >= @_START and c.`end` < @_END
        AND c.called != 'Anonymous' 
        AND CAST(c.`duration` AS UNSIGNED) > 30
        ORDER BY c.called, c.`end` limit 5000000)

    UNION ALL

        (SELECT c.called as customer, c.calling as company, MONTH(c.`end`) as 'MONTH',
                @G := 0 as Gain,
                @S := if(@nextComp <> c.calling AND @nextCust = c.called AND @nextDate = DATE_FORMAT(c.`end`,'%Y-%m-%d'), 1, 0) as Spill,
                @nextCust := c.called as nextCust,
                @nextComp := c.calling as nextComp,
                @nextDate := DATE_FORMAT(c.`end`,'%Y-%m-%d') as nextDate
        FROM cdrdata_archive c CROSS JOIN
             (SELECT @nextComp := 0, @nextCust := 0, @nextDate := 0) nextVals
        where c.`end` >= @_START and c.`end` < @_END
        AND c.called != 'Anonymous'
        AND CAST(c.`duration` AS UNSIGNED) > 30
        ORDER BY c.called, c.`end` desc limit 5000000)
    ) SG

     GROUP BY SG.company, SG.`MONTH`

) SGgrouped

JOIN products p ON p.Number = SGgrouped.company
Where p.id IN (@_PRODUCTS)
ORDER BY SGgrouped.`MONTH`

Here's the input:

CREATE TABLE `cdrdata_archive` (
  `CustomerID` varchar(10) DEFAULT NULL,
  `callid` varchar(20) NOT NULL,
  `called` varchar(14) DEFAULT NULL,
  `description` varchar(20) DEFAULT NULL,
  `calling` varchar(16) DEFAULT NULL,
  `dtn` varchar(14) DEFAULT NULL,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `answered` datetime DEFAULT NULL,
  `duration` varchar(5) DEFAULT NULL,
  `talktime` varchar(5) DEFAULT NULL,
  `tta` int(3) DEFAULT NULL,
  PRIMARY KEY (`callid`),
  UNIQUE KEY `callid_UNIQUE` (`callid`),
  KEY `called` (`called`,`start`,`end`),
  KEY `abandoned_from` (`calling`,`talktime`,`end`),
  KEY `called end` (`calling`,`end`),
  KEY `start` (`start`),
  KEY `calling` (`calling`),
  KEY `endcalling` (`end`,`calling`),
  KEY `productdur` (`calling`,`end`,`duration`),
  KEY `endcallingdur` (`end`,`calling`,`duration`),
  KEY `company` (`CustomerID`,`end`),
  KEY `calling end` (`end`,`called`,`calling`),
  KEY `end` (`end`) USING BTREE,
  KEY `call_id_desc` (`callid`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Here's the EXPLAIN statement:

enter image description here

Here's the output from SHOW VARIABLES LIKE '%buffer%';

enter image description here

Q: How can I optimize this query?

Upvotes: 1

Views: 108

Answers (2)

Rick James
Rick James

Reputation: 142538

I have given you a longer answer on another forum. In this forum, I see a puny key_buffer_size of only 8MB. Suggest you set that to about 20% of available RAM.

Upvotes: 1

user4420255
user4420255

Reputation:

Main problem is (see my comments above) is your table sucture like this TYPE CASTING

 AND CAST(c.`duration` AS UNSIGNED) > 30

MySQL can't use any KEY and have to scan the complete table.

EDIT

Please show a detailed explain

EXPLAIN 
       SELECT c.called as customer, c.calling as company, MONTH(c.`end`) as 'MONTH',
                @G := 0 as Gain,
                @S := if(@nextComp <> c.calling AND @nextCust = c.called AND @nextDate = DATE_FORMAT(c.`end`,'%Y-%m-%d'), 1, 0) as Spill,
                @nextCust := c.called as nextCust,
                @nextComp := c.calling as nextComp,
                @nextDate := DATE_FORMAT(c.`end`,'%Y-%m-%d') as nextDate
        FROM cdrdata_archive c CROSS JOIN
             (SELECT @nextComp := 0, @nextCust := 0, @nextDate := 0) nextVals
        where c.`end` >= @_START and c.`end` < @_END
        AND c.called != 'Anonymous'
        AND CAST(c.`duration` AS UNSIGNED) > 30
        ORDER BY c.called, c.`end` desc 
    limit 5000000

Upvotes: 2

Related Questions