以下是我的一个分页查询语句,但是执行这段代码居然需要将近2分钟的时间。希望高手们能帮忙优化一下
SELECT top 3 sms_mess.*,sms_user.us_name FROM sms_mess INNER JOIN sms_user on (sms_mess.uid=sms_user.uid)
WHERE phid NOT IN
(SELECT TOP 3 phid FROM sms_mess WHERE 1=1 AND (us_hile='1' OR us_hile like '%_1' OR us_hile like '%_1_%' OR us_hile like '1_%') ORDER BY send_time DESC)
AND (us_hile='1' OR us_hile like '%_1' OR us_hile like '%_1_%' OR us_hile like '1_%') ORDER BY send_time DESC
SELECT top 3 sms_mess.*,sms_user.us_name FROM sms_mess INNER JOIN sms_user on (sms_mess.uid=sms_user.uid)
WHERE phid NOT IN
(SELECT TOP 3 phid FROM sms_mess WHERE 1=1 AND (us_hile='1' OR us_hile like '%_1' OR us_hile like '%_1_%' OR us_hile like '1_%') ORDER BY send_time DESC)
AND (us_hile='1' OR us_hile like '%_1' OR us_hile like '%_1_%' OR us_hile like '1_%') ORDER BY send_time DESC
没见过!!!
hile
建立索引试试。
方法二是:保持现在的 sql 也行,但要建立几个索引
索引1 :uid
索引2 :第一字段send_time降序(这个不清楚),第二字段us_hile随便升降序吧理论上这样会 查询时间会 优化
SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
建立索引