where time BETWEEN date_add('2015-09-22 00:00:00', interval '1' day) and date_add('2015-09-22 23:59:59', interval '1' day)
and id1 in (select id2 from role.player
where CreateTime between '2015-09-22 00:00:00' and '2015-09-22 23:59:59')
复制代码
然后小弟看了不少优化的建议,得到一个初步的优化方案:先将子查询改为连接查询.
而偏偏第一次动手优化时出现了极大的反差,也许不是方案错,可能是小弟自己写错或者其他等原因,不过还是希望大神帮忙解惑一下,下面看我修改后的语句(当然修改过后,得到的答案是没错的):
select count(distinct b.id1) from
(
select player.id2,player.CreateTime
from role.player
where player.CreateTime between '2015-09-22 00:00:00' and '2015-09-22 23:59:59'
) a
inner join
(
select resource.id1,resource.time
from log.resource
where resource.time between date_add('2015-09-22 00:00:00', interval '1' day) and date_add('2015-09-22 23:59:59', interval '1' day)
) b
on b.id1 = a.id2
where time BETWEEN date_add('2015-09-22 00:00:00', interval '1' day) and date_add('2015-09-22 23:59:59', interval '1' day)
and id1 in ( select id2 from role.player
where CreateTime between '2015-09-22 00:00:00' and '2015-09-22 23:59:59' )
#执行时间1.17
select count(distinct b.id1) from
(select player.id2 from role.player where player.CreateTime between '2015-09-22 00:00:00' and '2015-09-22 23:59:59') a left join
(select resource.id1 from log.resource where resource.time between date_add('2015-09-22 00:00:00', interval '1' day) and date_add('2015-09-22 23:59:59', interval '1' day)) b
on a.id2 = b.id1
#执行时间1.24
select count(distinct resource.id1) from role.player left join log.resource
on player.id2 = resource.id1
where resource.time BETWEEN date_add('2015-09-22 00:00:00', interval '1' day) and date_add('2015-09-22 23:59:59', interval '1' day)
and player.CreateTime between '2015-09-22 00:00:00' and '2015-09-22 23:59:59'