select c.globalUid,c.badge,temp.sumB,temp.sumP from tb_user c,(select a.uid,sum(a.badge) as sumB,IFNULL(sum(b.propertyNum),0) as sumP,sum(a.badge)-IFNULL(sum(b.propertyNum),0) as cha from tb_buyfun_log a left join buyrecord b on a.uid=b.globaluid group by a.uid having cha>500) as temp where uptime>'2009-12-01 00:00:00' and globaluid=temp.uid
谁帮忙优化下这语句,我在电脑上运行后很慢,有时半天都不出来,有时连查询工具都搞起死掉,关也关不了, 各位高手帮忙看下好不?
谁帮忙优化下这语句,我在电脑上运行后很慢,有时半天都不出来,有时连查询工具都搞起死掉,关也关不了, 各位高手帮忙看下好不?
from tb_user c,
( select a.uid,sum(a.badge) as sumB,IFNULL(sum(b.propertyNum),0) as sumP,sum(a.badge)- IFNULL(sum(b.propertyNum),0) as cha
from tb_buyfun_log a left join buyrecord b on a.uid=b.globaluid
group by a.uid
having cha>500
) as temp
where uptime>'2009-12-01 00:00:00' and globaluid=temp.uid 本来子查询中就不应该用group by (最外层)你还多加了having 运行肯定慢啊!
考虑别的方式吧
from tb_user c,(
select a.uid,sum(a.badge) as sumB,
IFNULL(sum(b.propertyNum),0) as sumP,
sum(a.badge)-IFNULL(sum(b.propertyNum),0) as cha
from tb_buyfun_log a left join buyrecord b on a.uid=b.globaluid
group by a.uid
having cha>500
) as temp
where uptime>'2009-12-01 00:00:00' and globaluid=temp.uid
你这是个笛卡积啊。贴出你的
show index from tb_user
show index from tb_buyfun_log
show index from buyrecord
以及
explain select c.globalUid,c.badge,temp.sumB,temp.sumP
from tb_user c,(
select a.uid,sum(a.badge) as sumB,
IFNULL(sum(b.propertyNum),0) as sumP,
sum(a.badge)-IFNULL(sum(b.propertyNum),0) as cha
from tb_buyfun_log a left join buyrecord b on a.uid=b.globaluid
group by a.uid
having cha>500
) as temp
where uptime>'2009-12-01 00:00:00' and globaluid=temp.uid
列出tb_user表中puttime>2009-11-17 00:00:00,并且 tb_buyfun_log表中badge总和 - buyrecord 表中propertyNum总和 的值 大于100的玩家,显示出UID、badge总和、propertyNum总和3个表通过 globalUid(也就是 用户的全局id) 关联 也就是
tb_buyfun_log.uid=buyrecord.globaluid=tb_user.globaluid
tb_buyfun_log.uid=buyrecord.globaluid=tb_user.globaluid
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
敲命令 show index from 然后把内容贴出来。一共三个表的 show index
加一个 explain 如果不愿意贴就算了。
buyrecord 1 buytime 1 buytime A 63 YES BTREE
buyrecord 1 buytime 2 globalUid A 63 YES BTREE
buyrecord 1 buytime 3 propertyid A 63 YES BTREE
tb_buyfun_log 1 status 1 buytime A BTREE
tb_buyfun_log 1 status 2 status A BTREE
tb_buyfun_log 1 status 3 uid A BTREE
tb_user 1 rop 1 rop A 2 YES BTREE
tb_user 1 puttime 1 puttime A 1588 YES BTREE