MySql: Optimizing a query

Question

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?

Please execute this small query and show the result. It gave me a kind about cardinality of these two columns. SELECT count(DISTINCT end) , count(DISTINCT called) FROM cdrdata_archive — user4420255
– user4420255, Commented Feb 16, 2015 at 8:50
BTW: The key UNIQUE KEY callid_UNIQUE (callid), is not necessary, because of PRIMARY KEY. — user4420255
– user4420255, Commented Feb 16, 2015 at 8:54
BTW: The key KEY call_id_desc (callid) USING BTREE, is not necessary, because of PRIMRARY KEY. You can drop it. — user4420255
– user4420255, Commented Feb 16, 2015 at 9:02
Please show some other mysql parameter: "mysql>SHOW VARIABLES LIKE '%buffer%'; " — user4420255
– user4420255, Commented Feb 16, 2015 at 9:35
Thanks!. "key_buffer_size" is still default and to small. If using just MyISAM, set key_buffer_size to 20% of available RAM. (Plus innodb_buffer_pool_size=0) — user4420255
– user4420255, Commented Feb 16, 2015 at 9:50

score 2 · Accepted Answer · 2015-02-16 13:47:40Z

2

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

edited Feb 16, 2015 at 13:47

answered Feb 16, 2015 at 9:08

user4420255

Sign up to request clarification or add additional context in comments.

1 Comment

alex Over a year ago

tried to change it to AND c.duration > 30 but it adds about 2 seconds to the execution time.

Rick James · Accepted Answer · 2015-02-17 02:42:13Z

1

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.

answered Feb 17, 2015 at 2:42

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

MySql: Optimizing a query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related