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

在执行一个任务的时候报的异常.
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的时候不会去使用索引,速度不会快很多,现在不知道怎么搞了,大家帮忙想想办法,给个思路...

8 个解决方案

#1

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

#2

测试了
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效率不高

#3

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

#4

简单，做个调试，
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;

结果、记录数相加看看结果

#5

=.=!试了下,就光两个SQL语句就速度非常慢,每条SQL语句需要4-5分钟时间
麻烦...................

#6

要测试结果，只有等待了，建立索引没有？

#7



create index idx_type_recordtime on consume_log(type,recordtime);

#8

四天异常,今天又好了,搞不清楚,=.=!

#1