Question

求各位大神帮我把如下二句sql优化一下，页面查询速度太慢了

<!--搜索次数-->
<select id="getUserNumOfSearch" resultType="com.huawei.prettysearch.spi.entity.UserSearchEntry"
parameterType="Map">
select trunc(logtime ,'dd') AS logTime,
count(*) as search_count
from t_dsdp_sh_keyword_log
group by (userId,trunc(logtime ,'dd'))
having userId = #{userId} AND trunc(logtime,'dd')
between trunc(sysdate-interval '${count}' day ,'dd')
and trunc(sysdate-interval '1' day,'dd') order by trunc(logtime ,'dd')
</select>

如上图，查询速度太慢，怎么优化！

Answer 1

1、HAVING使用方法有误，将这些条件，放到WHERE中

2、GROUP BY中，UserID是否有必要？ 可以去掉-- 业务上，一般不这么干

SELECT trunc(logtime ,'dd') AS logTime,
	 	count(*) as search_count
FROM t_dsdp_sh_keyword_log 
WHERE userId = #{userId} 
AND trunc(logtime,'dd') between trunc(sysdate-interval '${count}' day ,'dd')
AND trunc(sysdate-interval '1' day,'dd') 
GROUP BY trunc(logtime ,'dd')
ORDER BY trunc(logtime ,'dd')

追问

感觉很 NB。。