两条速度相差1350倍的sql语句

xuehanxin

于 2011-12-13 15:04:14 发布

阅读量85

点赞数

分类专栏：点点数据库文章标签： sql优化 mysql 查询优化百度咨询

点点数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

select *from RESOURCES where  RES_STATUS = 1 and PLATFORM_FLAG=1 and  RES_ID in
    (select RES_ID from DOWNLOADRECORDS where DOWNLOADRECORDS_TIME > DATE_SUB(now(),INTERVAL 7 DAY) 
    group by RES_ID order by COUNT(*) desc ) limit 1, 10;

这条语句查处结果需要20250毫秒

  select bb.* from (select * from DOWNLOADRECORDS where DOWNLOADRECORDS_TIME > DATE_SUB(now(),INTERVAL 7 DAY) group by RES_ID order by COUNT(*) desc limit 1,10) as aa,RESOURCES as bb  where aa.RES_ID=bb.RES_ID  and bb.RES_STATUS = 1 and bb.PLATFORM_FLAG=1;

这条语句只需要15毫秒

相同的环境下，查出相同的结果速度相差1000倍