Geek Answers Handbook

Autocomplete query using FULLTEXT mysql index

I am trying to create a good match query to autocomplete a search box using the MATCH function to index mysql FULLTEXT. I would like it to be optimized with maximum performance and, if possible, to be flexible for typos and such (without much effort).

The search is performed for users, where each user has a name and a screen name. I would like to combine their combination. For example, if the username is “Guy Kawasaki” and the screen name is “gkawasaki”, then the request “gkawas” and “Guy K” will be sent to him. In addition, I would like the results to be sorted by the combination of the score for the match and the score that I keep in the table.

Currently, I have made two different requests:

SELECT *, MATCH (name) AGAINST ('+Guy +K*' IN BOOLEAN MODE) AS SCORE FROM users WHERE MATCH (name) AGAINST ('+Guy +K*' IN BOOLEAN MODE) ORDER BY SCORE, grade DESC LIMIT 5 SELECT *, MATCH (screen_name) AGAINST ('+Guy +K*' IN BOOLEAN MODE) AS SCORE FROM users WHERE MATCH (screen_name) AGAINST ('+Guy +K*' IN BOOLEAN MODE) ORDER BY SCORE, grade DESC LIMIT 5

I am not very happy that I am using two queries, and also not very sure about BOOLEAN MODE and all the "+" signs. How can I improve this? What is the best approach to implementing such auto-completion?

+4
source share
1 answer
 SELECT *, MATCH (name, screen_name) AGAINST ('+Guy +K*' IN BOOLEAN MODE) AS SCORE FROM users WHERE MATCH (name, screen_name) AGAINST ('+Guy +K*' IN BOOLEAN MODE) ORDER BY SCORE, grade DESC LIMIT 5

You can simply add column names to the MATCH () part of the query.

+2
source

More articles:


All Articles