SQL优化，求教？

select * from userpathlog where 
datediff(day,createtime,convert(datetime,'2011-11-11'))=0
 and (realurl like '%regmobile.aspx%' or realurl like '%register.aspx%' or realurl like '%regmail.aspx%')

select count(*) from userpathlog where 
datediff(day,createtime,convert(datetime,'2011-11-11'))=0
 and (realurl like '%regmobile.aspx%' or realurl like '%register.aspx%' or realurl like '%regmail.aspx%')

这两条相差一秒，上面要4秒，下面3秒，该如何优化？

SQL code

 createtime >= '2011-11-11' AND createtime<'2011-11-12'

加上索引，日期列createtime和realurl

SQL code

select count(*) from userpathlog where  
convert(char(10),createtime,121)='2011-11-11'
 and (realurl like '%regmobile.aspx%' or realurl like '%register.aspx%' or realurl like '%regmail.aspx%')

SQL code

select * from userpathlog where  
createtime>='2011-11-11'
AND createtime<'2011-11-12'
and (realurl like '%regmobile.aspx%' or realurl like '%register.aspx%' or realurl like '%regmail.aspx%')

select count(*) from userpathlog where  
createtime>='2011-11-11'
AND createtime<'2011-11-12'
and (realurl like '%regmobile.aspx%' or realurl like '%register.aspx%' or realurl like '%regmail.aspx%')

createtime加索引

realurl--這列的條件用不到索引，這是慢的原因啊，試試一下強制索引 with(index=索引名)

SQL code

select
 count(*) 
from
 userpathlog where  
createtime>='2011-11-11'
and
 createtime<'2011-11-12'
and
 (realurl like '%regmobile.aspx%' or realurl like '%register.aspx%' or realurl like '%regmail.aspx%')

谢谢各位了，我去试试

[sql_code]
select *
from userpathlog
where 0 = datediff(day, createtime, convert(datetime, '2011-11-11'))
  and 1 = case when realurl like '%regmobile.aspx%' then 1
  when realurl like '%register.aspx%' then 1
  when realurl like '%regmail.aspx%' then 1
  else 0 end

[/sql_code]

试试看

再不行，就用 exists 替换 when 条件

这是根本原因，其它的方法经测试，性能没什么差别，基本一样，没办法吗