I'm trying to create a good match query for an auto-complete search box using mysql FULLTEXT index MATCH capability. I would like it to be very performance optimized and if possible flexible to typos and such (without too much work).
The search is for users, where each user has a name and a screen name. I would like to match a combination of them. For example if the user name is "Guy Kawasaki" and the screen name is "gkawasaki" than the query "gkawas" and "Guy K" will direct to him. Also, I would like the results to be sorted by a combination of the match score and a grade I'm holding in the table.
Currently what I've done is two different queries:
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) A开发者_高级运维GAINST ('+Guy +K*' IN BOOLEAN MODE)
ORDER BY SCORE, grade DESC LIMIT 5
I'm not so happy with the fact that I'm using two queries and also not so sure regarding the BOOLEAN MODE and all the "+" signs. How can I improve this? What is the best approach to implement such an auto-complete?
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 just add the column names to the MATCH () part of the query.
精彩评论