opalepatrick

SELECT SQL_BIG_RESULT t2.state AS status, t1.ad_provider AS N, t1.adgroup, IF(SUBSTRING(t1.campaign, 1, 10) = 'Somethings', SUBSTRING_INDEX(t1.campaign,'_',3), t1.campaign) AS cam, SUM(t1.clicks) AS `clicks`, SUM(t1.sales_number) AS `salesno`, ROUND(SUM(t1.sale_value),2) AS `salestotal`, ROUND(SUM(t1.comm),2) AS `comm` , SUM(t1.ad_cost) AS `ad_cost`, ROUND(SUM(t1.comm)-SUM(t1.ad_cost), 2) AS `profit`, ROUND(SUM(t1.comm)/SUM(t1.sales_number), 2) AS `commpersale`, ROUND(SUM(t1.comm)/SUM(t1.clicks), 2) AS `commperclick`, ROUND(SUM(t1.cpc)/SUM(t1.clicks), 2) AS `cpc`, ROUND(SUM(t1.avgpos)/COUNT(*), 0) AS `avgpos`, ROUND((SUM(qs)/COUNT(*)), 1) AS `qs` 

FROM `db`.`table` AS t1 

LEFT JOIN `db`.`adgroup_status` AS t2 ON (t1.adgroup = t2.adgroup AND t1.campaign=t2.campaign) 

WHERE (t1.trans_date >= '2009-01-01' AND t1.trans_date <= '11-05-04' || ( (t1.click_date >= '2009-01-01' AND t1.click_date <= '11-05-04') AND t1.comm = 0 )) AND t1.campaign LIKE 'Somethings_UK_D-I%' AND t1.campaign NOT LIKE 'Sup%' 

GROUP BY N, cam, t1.adgroup ORDER BY `profit`


| id | select_type | table | type   | possible_keys                  | key     | key_len | ref                                  | rows   | Extra                                        |
+----+-------------+-------+--------+--------------------------------+---------+---------+--------------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | t1    | ALL    | campaign,trans_date,click_date | NULL    | NULL    | NULL                                 | 732112 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY,adgroup                | PRIMARY | 604     | db.t1.adgroup,db.t1.campaign |      1 |                                              |


CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `click_date` date NOT NULL DEFAULT '0000-00-00',
  `trans_date` date NOT NULL DEFAULT '0000-00-00',
  `ad_provider` varchar(255) NOT NULL DEFAULT '',
  `keyword` varchar(255) NOT NULL DEFAULT '',
  `campaign` varchar(255) NOT NULL DEFAULT '',
  `adgroup` varchar(100) NOT NULL DEFAULT '',
  `creative` varchar(255) NOT NULL DEFAULT '',
  `sales_number` int(5) NOT NULL DEFAULT '0',
  `sale_value` decimal(10,2) NOT NULL DEFAULT '0.00',
  `comm` decimal(10,2) NOT NULL DEFAULT '0.00',
  `ad_cost` decimal(10,2) NOT NULL DEFAULT '0.00',
  `clicks` int(5) NOT NULL DEFAULT '0',
  `imp` int(5) NOT NULL DEFAULT '0',
  `cpc` decimal(5,2) DEFAULT NULL,
  `ctr` decimal(5,2) DEFAULT NULL,
  `avgpos` decimal(5,2) DEFAULT NULL,
  `qs` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `campaign` (`campaign`),
  KEY `trans_date` (`trans_date`),
  KEY `adgroup` (`adgroup`),
  KEY `click_date` (`click_date`) USING BTREE,
  KEY `keyword` (`keyword`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=13206581 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

CREATE TABLE `adgroup_status` (
  `state` varchar(50) DEFAULT NULL,
  `adgroup` varchar(120) NOT NULL DEFAULT '',
  `campaign` varchar(80) NOT NULL DEFAULT '',
  PRIMARY KEY (`adgroup`,`campaign`),
  KEY `adgroup` (`adgroup`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8