sql中同一条sql语句 只是查询条件中的时间不同但是执行速度差别很大（就是在一个时间段内慢，大于这个时间段就没有关问题）
sql中同一条sql语句 只是查询条件中的时间不同但是执行速度差别很大（就是在一个时间段内慢，大于这个时间段就没问题）
select * from wcm_shanghai_userStatus us
inner join wcm_shanghai_userTax ut on us.user_id=ut.user_id
inner join wcm_product_buy pb on us.user_id=pb.user_id and us.service_id =pb.product_id
where ut.sh_finance_id=5
and us.sh_status>2
and pb.buy_time between '2011-01-01 0:00:00' and '2012-03-22 0:00:00'
这个查询的数据有(1446 行受影响) 用时00:00:00
select * from wcm_shanghai_userStatus us
inner join wcm_shanghai_userTax ut on us.user_id=ut.user_id
inner join wcm_product_buy pb on us.user_id=pb.user_id and us.service_id =pb.product_id
where ut.sh_finance_id=5
and us.sh_status>2
and pb.buy_time between '2011-09-15 0:00:00' and '2012-03-22 0:00:00'
这个查询的数据有(647 行受影响)用时00:03:34
跪求高手指导
------解决方案--------------------
是不是表有索引碎片，把wcm_product_buy表的buy_time 索引重建一下
------解决方案--------------------
给这个时间字段加上索引试试！
------解决方案--------------------
1.可考虑对pb.buy_time建立索引.
2.语句可更改为如下:
select * from wcm_shanghai_userStatus us
inner join wcm_shanghai_userTax ut on us.user_id=ut.user_id
inner join wcm_product_buy pb on us.user_id=pb.user_id and us.service_id =pb.product_id
where ut.sh_finance_id=5
and us.sh_status>2
and datediff(dd,''2011-01-01',pb.buy_time) >= 0 and datediff(dd,'2012-03-22',pb.buy_time) <= 0
select * from wcm_shanghai_userStatus us
inner join wcm_shanghai_userTax ut on us.user_id=ut.user_id
inner join wcm_product_buy pb on us.user_id=pb.user_id and us.service_id =pb.product_id
where ut.sh_finance_id=5
and us.sh_status>2
and pb.buy_time between '2011-01-01 0:00:00' and '2012-03-22 0:00:00'
这个查询的数据有(1446 行受影响) 用时00:00:00
select * from wcm_shanghai_userStatus us
inner join wcm_shanghai_userTax ut on us.user_id=ut.user_id
inner join wcm_product_buy pb on us.user_id=pb.user_id and us.service_id =pb.product_id
where ut.sh_finance_id=5
and us.sh_status>2
and pb.buy_time between '2011-09-15 0:00:00' and '2012-03-22 0:00:00'
这个查询的数据有(647 行受影响)用时00:03:34
跪求高手指导
------解决方案--------------------
是不是表有索引碎片，把wcm_product_buy表的buy_time 索引重建一下
------解决方案--------------------
给这个时间字段加上索引试试！
------解决方案--------------------
1.可考虑对pb.buy_time建立索引.
2.语句可更改为如下:
select * from wcm_shanghai_userStatus us
inner join wcm_shanghai_userTax ut on us.user_id=ut.user_id
inner join wcm_product_buy pb on us.user_id=pb.user_id and us.service_id =pb.product_id
where ut.sh_finance_id=5
and us.sh_status>2
and datediff(dd,''2011-01-01',pb.buy_time) >= 0 and datediff(dd,'2012-03-22',pb.buy_time) <= 0