一条“稍”复杂的sql 求改编一下 现在有点慢。。。。

Question

CREATE TABLE `tb_single_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `singleUserId` varchar(50) DEFAULT NULL,
  `provider` varchar(50) DEFAULT NULL,
  `providerCountry` varchar(50) DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `developer` varchar(50) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `version` varchar(50) DEFAULT NULL,
  `productType` varchar(50) DEFAULT NULL,
  `units` int(50) DEFAULT NULL,
  `developerProceeds` varchar(50) DEFAULT NULL,
  `beginDate` timestamp NULL DEFAULT NULL,
  `endDate` timestamp NULL DEFAULT NULL,
  `customerCurrency` varchar(50) DEFAULT NULL,
  `countryCode` varchar(50) DEFAULT NULL,
  `currencyofProceeds` varchar(50) DEFAULT NULL,
  `appleIdentifier` varchar(50) DEFAULT NULL,
  `customerPrice` double(20,2) DEFAULT NULL,
  `originalPrice` double(20,2) DEFAULT NULL,
  `promoCode` varchar(50) DEFAULT NULL,
  `parentIdentifier` varchar(50) DEFAULT NULL,
  `subscription` varchar(50) DEFAULT NULL,
  `period` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=40230 DEFAULT CHARSET=utf8
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('1','4','APPLE','US','1.0.0','OURPALM','新观音灵签®','1.0.4','1','1','0','2012-09-03 00:00:00','2012-09-03 00:00:00','HKD','HK','HKD','491635124','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('2','4','APPLE','US','蠢蛋秀™(iPhone)','OURPALM','蠢蛋秀™','1.1.0','7','5','0','2012-09-03 00:00:00','2012-09-03 00:00:00','USD','MY','USD','468950984','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('3','4','APPLE','US','蠢蛋秀™(iPhone)','OURPALM','蠢蛋秀™','1.1.0','7','7','0','2012-09-03 00:00:00','2012-09-03 00:00:00','USD','MO','USD','468950984','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('4','4','APPLE','US','蠢蛋秀™(iPhone)','OURPALM','蠢蛋秀™','1.1.0','7','1','0','2012-09-03 00:00:00','2012-09-03 00:00:00','GBP','GB','GBP','468950984','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('5','4','APPLE','US','蠢蛋秀™(iPhone)','OURPALM','蠢蛋秀™','1.1.0','1','1','4.2','2012-09-03 00:00:00','2012-09-03 00:00:00','CNY','CN','CNY','468950984','0.66','6.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('6','4','APPLE','US','蠢蛋秀™(iPhone)','OURPALM','蠢蛋秀™','1.1.0','7','19','0','2012-09-03 00:00:00','2012-09-03 00:00:00','HKD','HK','HKD','468950984','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('7','4','APPLE','US','Angry Piggy℗','OURPALM','Angry Piggy℗','1.0.3','7','1','0','2012-09-03 00:00:00','2012-09-03 00:00:00','CAD','CA','CAD','488175405','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('8','4','APPLE','US','200912301557','OURPALM','Colorful Bubble','1.0.3','1','3','0','2012-09-03 00:00:00','2012-09-03 00:00:00','USD','US','USD','348674514','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('9','4','APPLE','US','蠢蛋秀™HD','OURPALM','蠢蛋秀™HD','1.0.0','1T','1','0','2012-09-03 00:00:00','2012-09-03 00:00:00','AUD','AU','AUD','478746885','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('10','4','APPLE','US','Angry Piggy℗','OURPALM','Angry Piggy℗','1.0.3','7','1','0','2012-09-03 00:00:00','2012-09-03 00:00:00','USD','TH','USD','488175405','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('11','4','APPLE','US','Angry Piggy℗','OURPALM','Angry Piggy℗','1.0.3','1','2','0','2012-09-03 00:00:00','2012-09-03 00:00:00','USD','LB','USD','488175405','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('12','4','APPLE','US','Angry Piggy℗','OURPALM','Angry Piggy℗','1.0.3','1','1','0','2012-09-03 00:00:00','2012-09-03 00:00:00','USD','IL','USD','488175405','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('13','4','APPLE','US','Angry Piggy℗','OURPALM','Angry Piggy℗','1.0.3','1','1','0','2012-09-03 00:00:00','2012-09-03 00:00:00','USD','TR','USD','488175405','0.00','0.00',NULL,NULL,NULL,NULL);
insert into `tb_single_product` (`id`, `singleUserId`, `provider`, `providerCountry`, `sku`, `developer`, `title`, `version`, `productType`, `units`, `developerProceeds`, `beginDate`, `endDate`, `customerCurrency`, `countryCode`, `currencyofProceeds`, `appleIdentifier`, `customerPrice`, `originalPrice`, `promoCode`, `parentIdentifier`, `subscription`, `period`) values('14','4','APPLE','US','Angry Piggy℗','OURPALM','Angry Piggy℗','1.0.3','1','2','0','2012-09-03 00:00:00','2012-09-03 00:00:00','EUR','ES','EUR','488175405','0.00','0.00',NULL,NULL,NULL,NULL);
 SHOW INDEX FROM tb_single_product 
`Table`, `Non_unique`, `Key_name`, `Seq_in_index`, `Column_name`, `Collation`, `Cardinality`, `Sub_part`, `Packed`, `Null`, `Index_type`, `Comment`'tb_single_product','0','PRIMARY','1','id','A','38183',NULL,NULL,'','BTREE',''现在才几万的数据  就要14秒左右  这个数据还要同前一天数据进行比较 中间还有一些数据上的处理  一个查询35秒这有点受不了了   目前一天大约有1300条左右入表  到半年后不得卡死啊。。  求指导

Answer 1

忘了。。 sorry
SELECT
        a.title,
        COALESCE(units,
        0) units,
        COALESCE(customerPrice,
        0) customerPrice,
        COALESCE(propsPrice,
        0) propsPrice,
        COALESCE(updateCount,
        0) AS updateCount,
        COALESCE(refund,
        0) AS refund,
        a.beginDate,
        (SELECT
            SUM(units) units 
        FROM
            tb_single_product   
        WHERE
            beginDate BETWEEN'2012-09-27' AND '2012-09-27'   
            AND title = a.title  
            AND (
                productType='1' 
                OR productType='1F' 
                OR productType='1T' 
                OR productType='F1' 
            ) 
        GROUP BY
            countryCode,
            title 
        ORDER BY
            units  DESC  LIMIT 1) AS maxdown,
        (SELECT
            countryCode 
        FROM
            tb_single_product 
        WHERE
            title=a.title  
            AND units=a.maxdown  
            AND beginDate BETWEEN'2012-09-27' AND '2012-09-27'  LIMIT 1) AS maxdownCode 
    FROM
        (SELECT
            title,
            MAX(units) AS maxdown,
            MAX(beginDate) AS beginDate 
        FROM
            tb_single_product  
        WHERE
            beginDate BETWEEN'2012-09-27' AND '2012-09-27'   
            AND (
                productType!='IA1' 
                AND productType!='IA9' 
                AND productType!='IAY' 
                AND productType!='FI1'
            ) 
        GROUP BY
            title ) a 
    LEFT  JOIN
        (
            SELECT
                SUM(units*customerPrice)customerPrice,
                (SELECT
                    SUM(units*customerPrice)  
                FROM
                    tb_single_product  
                WHERE
                    units>0  
                    AND parentIdentifier = tb.sku    
                    AND beginDate BETWEEN'2012-09-27' AND '2012-09-27'    
                    AND (
                        productType='IA1' 
                        OR productType='IA9' 
                        OR productType='IAY' 
                        OR productType='FI1'
                    )) propsPrice,
                title,
                sku 
            FROM
                tb_single_product tb  
            WHERE
                beginDate BETWEEN'2012-09-27' AND '2012-09-27'  
                AND (
                    productType!='IA1' 
                    AND productType!='IA9' 
                    AND productType!='IAY' 
                    AND productType!='FI1'
                )   
                AND units>0  
            GROUP BY
                title ) cp 
                    ON a.title = cp.title  
            LEFT JOIN
                (
                    SELECT
                        SUM(units) units,
                        title 
                    FROM
                        tb_single_product 
                    WHERE
                        (
                            productType='1' 
                            OR productType='1F' 
                            OR productType='1T' 
                            OR productType='F1' 
                        )  
                        AND beginDate BETWEEN'2012-09-27' AND '2012-09-27'   
                    GROUP BY
                        title 
                ) sp 
                    ON cp.title = sp.title  
            LEFT JOIN
                (
                    SELECT
                        title,
                        SUM(units) updateCount 
                    FROM
                        tb_single_product 
                    WHERE
                        (
                            productType='7' 
                            OR productType='7F' 
                            OR productType='7T' 
                            OR productType='F7'
                        )  
                        AND beginDate BETWEEN'2012-09-27' AND '2012-09-27'   
                    GROUP BY
                        title 
                ) edit 
                    ON cp.title = edit.title  
            LEFT JOIN
                (
                    SELECT
                        title,
                        SUM(units*customerPrice) refund,
                        parentIdentifier 
                    FROM
                        tb_single_product 
                    WHERE
                        units<0  
                        AND beginDate BETWEEN'2012-09-27' AND '2012-09-27'  
                    GROUP BY
                        parentIdentifier 
                ) refund 
                    ON cp.sku = refund.parentIdentifier 

Answer 2

你跑一下我的SQL就知道了