按照这方法:http://www.itooy.com/p_408.html
做了个多关键词搜索功能,但是效率太低,sql要一秒多才执行完SELECT title,titlepic,smalltext,( IF(LOCATE('大电影', oldtitle), 4, 0) + IF(LOCATE('大电影', smalltext), 2, 0) + IF(LOCATE('开心超人', oldtitle), 4, 0) + IF(LOCATE('开心超人', smalltext), 2, 0) + IF(LOCATE('超人', oldtitle), 4, 0) + IF(LOCATE('超人', smalltext), 2, 0) + IF(LOCATE('开心', oldtitle), 4, 0) + IF(LOCATE('开心', smalltext), 2, 0) ) AS score FROM phome_ecms_download WHERE titlepic !='' AND ( (oldtitle LIKE '%大电影%') OR (smalltext LIKE '%大电影%') OR (oldtitle LIKE '%开心超人%') OR (smalltext LIKE '%开心超人%') OR (oldtitle LIKE '%超人%') OR (smalltext LIKE '%超人%') OR (oldtitle LIKE '%开心%') OR (smalltext LIKE '%开心%') ) ORDER BY score DESC limit 0,24
加上排序“ORDER BY score DESC”后速度变得很慢
求大神优化sql
做了个多关键词搜索功能,但是效率太低,sql要一秒多才执行完SELECT title,titlepic,smalltext,( IF(LOCATE('大电影', oldtitle), 4, 0) + IF(LOCATE('大电影', smalltext), 2, 0) + IF(LOCATE('开心超人', oldtitle), 4, 0) + IF(LOCATE('开心超人', smalltext), 2, 0) + IF(LOCATE('超人', oldtitle), 4, 0) + IF(LOCATE('超人', smalltext), 2, 0) + IF(LOCATE('开心', oldtitle), 4, 0) + IF(LOCATE('开心', smalltext), 2, 0) ) AS score FROM phome_ecms_download WHERE titlepic !='' AND ( (oldtitle LIKE '%大电影%') OR (smalltext LIKE '%大电影%') OR (oldtitle LIKE '%开心超人%') OR (smalltext LIKE '%开心超人%') OR (oldtitle LIKE '%超人%') OR (smalltext LIKE '%超人%') OR (oldtitle LIKE '%开心%') OR (smalltext LIKE '%开心%') ) ORDER BY score DESC limit 0,24
加上排序“ORDER BY score DESC”后速度变得很慢
求大神优化sql
数据库已经上传到微云
请大神帮忙优化sql
数据表完整打包了,请大神再帮忙看看