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

a312983516 2012-09-29 12:01:28 


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条左右入表 到半年后不得卡死啊。。 求指导
...全文
238 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
heavstar 2012-10-10
  • 打赏
  • 举报
 回复
用临时表分步来
a312983516 2012-09-29
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 的回复:]
你还不如说你想要什么结果，看能不能重写。
[/Quote]

你跑一下我的SQL就知道了
一起混吧 2012-09-29
  • 打赏
  • 举报
 回复
你还不如说你想要什么结果，看能不能重写。
a312983516 2012-09-29
  • 打赏
  • 举报
 回复
是不是代码带多了 都不爱深看了 。。。


那给个大盖的方法吧 像这种通过左关联拼成的数据 有什么办法加快速度呢
a312983516 2012-09-29
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 的回复:]
表的嵌套连接太多。
[/Quote]

啊 但是怎么改！
Rotel-刘志东 2012-09-29
  • 打赏
  • 举报
 回复
表的嵌套连接太多。
a312983516 2012-09-29
  • 打赏
  • 举报
 回复
里面逻辑有点复杂 应该可以看出来个大概吧 就是查询一些东西 然后组合成一套数据

要是我说的不够透彻请说下 我就详细的写一下
a312983516 2012-09-29
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 的回复:]
你的查询语句呢？
[/Quote]

忘了。。 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
Rotel-刘志东 2012-09-29
  • 打赏
  • 举报
 回复
语句是怎样的，具体的执行计划贴出。
wwwwb 2012-09-29
  • 打赏
  • 举报
 回复
什么问题，插入慢，还是比较慢
ACMAIN_CHM 2012-09-29
  • 打赏
  • 举报
 回复
你的查询语句呢？
cesium三维实景社区警务可视化平台全栈式开发视频教程
本项目为真实项目需求改编，全程带领项目的环境搭建，前端开发，后端开发，三维cesium实景开发，数据处理，工具使用，特别适合没有webgis项目开发经验的刚入行webgis三维实景开发的开发人员，能为找工作提供点开发...
包拯断案 | 别再让慢sql背锅@还故障一个真相
快来领取DBA运维大法，保你“断案如神”~
简单英译汉SQL脚本
create table t_dictionary(word varchar(200), trans varchar(1000)); insert into t_dictionary values('abandon','v.抛弃，放弃'); insert into t_dictionary values('abandonment','n.放弃'); insert into t_dictionary
自己改编的用来抓堵塞好用的SQL脚本(转)
因为所处的工作原因，经常会处理SQL运行慢的情况，需要抓出那条捣乱的虫子以表示自己的清白，所以很无奈的看了网上N多的资料，然后try了N+N遍，加上客户的试用，哈，一个比较完整的抓阻塞的脚本就出炉了。[@more@]来源：htt...
mariadb sql_SQL教程：在MariaDB上学习SQL
mariadb sql MariaDB是MySQL的流行分支，由MySQL原始版本的主要作者Monty Widenius创立。 正如Serdar Yegulalp在他的MariaDB教程中解释的那样：MariaDB入门 ，MariaDB具有“默认包含的许多强大功能，而不是仅作为附件提供，并且许多性能，可用性和安全性改进不能保证在其中显示出来。 MySQL。” 在本文中，我将向您展示如何使用S...
MySQL

56,687

社区成员

56,710

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章