报错:Communications link failure Last packet sent to the server was 563 ms ago,同事说SQL语句效率不够高,帮忙看下SQL语句及优化

x03570227 2008-06-27 03:57:55
在执行一个任务的时候报的异常.
Communications link failure Last packet sent to the server was 563 ms ago.

原SQL语句:
select sum(amount) as total from (select amount,userId from consume_log where type =1001 and recordtime<1214524800 and recordtime>=1212278400 union all select amount,userId from consume_log where type =2001 and recordtime<1214524800 and recordtime>=1212278400) as temp group by userId;

用mysql工具直接在服务器上查询所耗时间:293秒,五分钟左右,查询结果数据:520000+
现在想优化下这个SQL语句,偿试过改成
select sum(amount) as total from (select amount,userId
from consume_log where type =1001 or type=2001 and recordtime<1214524800 and recordtime>=1212278400 )
as temp group by userId;
结果时间更长,后面同事告诉我,使用or的时候不会去使用索引,速度不会快很多,现在不知道怎么搞了,大家帮忙想想办法,给个思路...
...全文
2299 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
x03570227 2008-07-01
  • 打赏
  • 举报
回复
四天异常,今天又好了,搞不清楚,=.=!
懒得去死 2008-06-27
  • 打赏
  • 举报
回复

create index idx_type_recordtime on consume_log(type,recordtime);
WWWWA 2008-06-27
  • 打赏
  • 举报
回复
要测试结果,只有等待了,建立索引没有?
x03570227 2008-06-27
  • 打赏
  • 举报
回复
=.=!试了下,就光两个SQL语句就速度非常慢,每条SQL语句需要4-5分钟时间
麻烦...................
WWWWA 2008-06-27
  • 打赏
  • 举报
回复
简单,做个调试,
select sum(amount) as total from consume_log where type=1001 and recordtime <1214524800 and recordtime>=1212278400
group by userId;

select sum(amount) as total from consume_log where type=2001 and recordtime <1214524800 and recordtime>=1212278400
group by userId;

结果、记录数相加看看结果
x03570227 2008-06-27
  • 打赏
  • 举报
回复
select sum(amount) as total from consume_log where type in(1001,2001) and recordtime <1214524800 and recordtime>=1212278400
group by userId;
这句的测试结果是278秒,521021条记录
=.=! 郁闷了,工具看不出结果,只能看到记录条数,现在不知道查询出来的东西是不是正常的
还有没有办法优化下啊,给个思路咯
x03570227 2008-06-27
  • 打赏
  • 举报
回复
测试了
select sum(amount) as total from (select amount,userId from consume_log where type =1001 and recordtime <1214524800 and recordtime>=1212278400 union all select amount,userId from consume_log where type =2001 and recordtime <1214524800 and recordtime>=1212278400) as temp group by userId;

select sum(amount) as total from (select amount,userId
from consume_log where type =1001 or type=2001 and recordtime <1214524800 and recordtime>=1212278400 )
as temp group by userId;
下面这句果然不行,花了1274秒,查询结果增加到1328993,是不是两个查询语句结果就不一样导致时间相差很大,还是用or的效果确实差

select sum(amount) as total from consume_log where type in(1001,2001) and recordtime <1214524800 and recordtime>=1212278400
group by userId;这句还没有测试过,不过感觉好不到哪里去,用in效率不高
WWWWA 2008-06-27
  • 打赏
  • 举报
回复
try:
select sum(amount) as total, from consume_log where type in(1001,2001) and recordtime <1214524800 and recordtime>=1212278400
group by userId;

56,912

社区成员

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

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