开发者

Optimizing auto-complete FULLTEXT SQL query

开发者 https://www.devze.com 2023-04-03 22:04 出处:网络
I have the following query which is used in order to do an auto-complete of a search box: SELECT *, MATCH (screen_name, name) AGAINST (\'+query*\' IN BOOLEAN MODE) AS SCORE

I have the following query which is used in order to do an auto-complete of a search box:

  SELECT *, MATCH (screen_name, name) AGAINST ('+query*' IN BOOLEAN MODE) AS SCORE 
  FROM users 
  WHERE MATCH (screen_name, name) AGAINST ('+query*' IN BOOLEAN MODE) 
  ORDER BY SCORE DESC LIMIT 3

I also have a FULL TEXT index on screen_name & name (together). When this table was relatively small (50k) this worked gre开发者_如何学运维at. Now the table is ~200k and it takes seconds(!) to complete each query. I'm using MySql MyISAM. Is this reasonable? What directions might I check in order to improve this as surely it doesn't satisfy the needs of an auto-complete query.


MYSQL Match against is really slow, you should look into alternatives like Sphinx Search Server.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号