14M rows? 10% of the tables discussed on these forums are bigger than that. 300 million will be 'huge'.
> PRIMARY KEY (`EntryDate`,`ChannelID`),
Can two rows for the same Channel be inserted with the same DATETIME? (Having any kind of date in a PRIMARY or UNIQUE key is a red flag in my book.)
(You have...)
SELECT dt.GroupID AS ChannelID, dt.GroupName AS Description,
dt.GroupDescription AS DeviceDescription,
GROUP_CONCAT(DATE_FORMAT(`dt`.`Date`, '%M %e, %Y %k:%i:00') ORDER BY `dt`.`Date` SEPARATOR '|') AS `EntryDate`,
GROUP_CONCAT(dt.Total ORDER BY `dt`.`Date` SEPARATOR '|') AS `Data`,
GROUP_CONCAT(dt.Rate ORDER BY `dt`.`Date` SEPARATOR '|') AS `RateData`,
SUM(dt.Total) AS ChannelTotal, SUM(`dt`.`Total`) * 0.34 AS `ChannelTotalCO2`,
SUM(`dt`.`TotalCost`) AS ChannelTotalCost
FROM
( SELECT SUM(d.`DataVal`)/1 AS Total, g.GroupID,
g.`GroupName`, g.`GroupDescription`,
`d`.`EntryDate` AS `Date`,
SUM(IFNULL(`sr`.Rate, 0) * (d.DataVal/1)) AS `Rate`,
SUM(IFNULL(`sr`.Rate, 0) * (d.DataVal/1)) AS `TotalCost`
FROM master.`channelgroup` cg
LEFT JOIN eddie.`groups` g ON cg.`GroupID` = g.`GroupID`
LEFT JOIN `data` d ON cg.ChannelID = d.`ChannelID`
LEFT OUTER JOIN `master`.`siterates` `sr` ON `d`.`SiteRateID` = `sr`.`SiteRateID`
WHERE (FIND_IN_SET(g.`GroupID`,'157, 78,87,79,91,81'))
AND ((d.EntryDate BETWEEN '2013-01-01 00:00:00' AND '2013-02-15 23:59:59'))
GROUP BY g.GroupID, DATE_FORMAT(`d`.`EntryDate`, '%M %e, %Y %k:%i:00')
) AS dt
GROUP BY dt.GroupID
ORDER BY SUM(dt.Total) DESC;
> WHERE (FIND_IN_SET(g.`GroupID`,'157, 78,87,79,91,81'))
The performance of this worries me. If `GroupID` is one of those number, then this is much better:
WHERE g.GroupID IN (157, 78,87,79,91,81)
Please provide the EXPLAIN for the query.
> all join tables are indexed appropriately.
The EXPLAIN will reveal that.