Optimizing a MySql query 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 &lt;&gt; 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` &gt;= @_START and c.`end` &lt; @_END
        AND c.called != 'Anonymous' 
        AND CAST(c.`duration` AS UNSIGNED) &gt; 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 &lt;&gt; 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` &gt;= @_START and c.`end` &lt; @_END
        AND c.called != 'Anonymous'
        AND CAST(c.`duration` AS UNSIGNED) &gt; 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:



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



Q: How can I optimize this query?
Based on the EXPLAIN, I would recommend focusing on second SELECT in the UNION.  It is probably the slowest piece, and the hardest to optimize.  Can you explain what it is doing?

One thing to try is to use literals in place of @_START and @_END.  @variables, even if constant, do not optimize well.

You have a lot of non-sargable expressions in the WHERE clauses.  See http://en.wikipedia.org/wiki/Sargable
Rewrite them.  One of note is that

@prevDate = DATE_FORMAT(c.`end`,'%Y-%m-%d')


is the same as

@prevDate = c.`end`


because of the way DATETIME works.  Fix all the non-sargable expressions; that will give the optimizer more options.

You would be better turning this into INT:

`duration` varchar(5) DEFAULT NULL,


As a VARCHAR it is useless in an index.  If you need non-integer values, either use NULL or have an extra column.

Please explain what the query is supposed to do -- in English.

Is limit  5000000 real?  Or an optimization trick?

What version of MySQL are you using?

(called, callid, and calling look so much alike that the query is hard to read.)

If you must name the INDEXes, at least be consistent:

KEY `called end` (`calling`,`end`),


Actually (called, end) might be better, at least for Derived #3.  I can't tell, but this might be the quickest partial fix.  It might avoid a 'filesort' necessary for

ORDER BY  c.called, c.`end`


However, due to the mixing of ASC and DESC, this won't be helped:

ORDER BY  c.called, c.`end` desc


Will it run any faster without @S := and @G :=?  These seem to do nothing inside the query, but perhaps lead to some kind of 'output' from the whole query.  And perhaps their presence forces the optimizer to do something sub-optimal.

Carrying data from one row to the next (re: @nextComp) is likely to be an optimization-killer.  One trick to avoid such things, is to have a sequence number (perhaps an AUTO_INCREMENT in a temp table) that lets you do a "self JOIN" to match up a row and the preceding row.  This is complex, but potentially beneficial.

You have a lot of redundant indexes; DROP them.  (This won't help the question in hand.)

After you have fixed what you can, post the new SELECT and the new CREATE TABLE.  Then we can continue this discussion.