优化查询时间。

[runnerweb]

info表有8w条信息，最新通过审核（flag=1）的求购信息。。info_id 为自动增加字段，dateandtime为添加时间。格式：2008-08-08 24:00:00

select top 10 showname,company from info where info_id in (select max(info_id) as info_id from info group by company ) and flag=1 order by dateandtime desc

[dawugui]

--估计差不多
select top 10 t.showname,t.company from info t where flag = 1 and info_id = (select max(info_id) as info_id from info where company = t.company) order by dateandtime desc 

[pt1314917]

dateandtime
在时间上建个索引试试``

[dc1728]

用这个：这个语句表面逻辑上是扫描表两次，但是实际上优化器处理后只扫描了一次。
select top 10 showname,company from info,(select company,max(info_id) as info_id from info group by company) as Temp
where info.info_id= Temp.info_id and info.company = Temp.company order by dateandtime desc

[dc1728]

少了一个表前缀，完整测试：
select top 10 info.showname,info.company from info,(select company,max(info_id) as info_id from info group by company) as Temp
where info.info_id= Temp.info_id and info.company = Temp.company order by dateandtime desc

[knowledge_Is_Life]

关注 接分

[UltraBejing]

什么呀