使用全文搜索代替like(mysql)

cmsware越用越慢啊,不优化不行了,开了慢查询,看到有以下大量的查询:

select URL FROM cmsware_publish_log where FileName LIKE '%img200904172058390_58x58.jpg';# Time: 100714  2:06:18

于是尝试使用索引来提高效率,加了一个全文索引,没有效果,好像对于%xxx 这样的查询,索引是没没有效果的. 于是在网上找了一下:

mysql> explain select logID FROM cmsware_publish_log where match(FileName) against ('*22_522704_a31ed5e97bd4097_60x90.png'); +----+-------------+---------------------+----------+---------------+----------+---------+------+------+-------------+ | id | select_type | table               | type     | possible_keys | key      | key_len | ref  | rows | Extra       | +----+-------------+---------------------+----------+---------------+----------+---------+------+------+-------------+ |  1 | SIMPLE      | cmsware_publish_log | fulltext | filename      | filename | 0       |      |    1 | Using where | +----+-------------+---------------------+----------+---------------+----------+---------+------+------+-------------+

说是使用全文搜索代替like,会好很多,于是测试了一下,效果还是不错的,至少从我这里单命令行测试,是快了很多,于是修改functions.php5.php这个文件,把 $result=$db->getRow("select URL FROM $table->publish_log where FileName LIKE '%{$searchFileName}' "); 修改为: $result=$db->getRow("select URL FROM $table->publish_log where match(FileName) against ('*{$searchFileName}') "); 一共是两处,虽然改了,但还是没有发现速度有明显提升.继续找原因,至少这个可以提高不少吧.

View this post on my blog