我以这种方式创建了全文索引:
CREATE FULLTEXT INDEX filter_index
ON posts (filter)
我目前用于搜索的代码是这样的:
SELECT * FROM posts
WHERE MATCH(filter) AGAINST (?)
该查询执行确定,并且在我的表中,我具有以下结构。
ID | name | from | filter
1 John Paris - France | John Paris France
2 Carey Paris - France | Carey Paris France
3 Maria France - Vion | Maria France Vion
4 Alfredo US New York | Alfredo US New York
5 JohnD Colombia | JohnD Colombia
如果我查询以下内容:
SELECT * FROM posts
WHERE MATCH(filter) AGAINST ('France Vion')
这将使我首先得到结果:John Paris France & Carey Paris France
只有在得到之后Maria France Vion
。
这不应该不同吗?首先是,Maria France Vion
然后才是其他结果?
如何优化此操作?
试试这个
SELECT *, MATCH(filter) AGAINST('France Vion' IN BOOLEAN MODE) as relevance
FROM posts_locale
WHERE MATCH(filter) AGAINST('France Vion' IN BOOLEAN MODE)
ORDER BY relevance DESC
另外,如果您要搜索两个不同的字段并赋予优先级。喜欢搜索from
,filter
但优先考虑from
归档
SELECT *,
MATCH(filter) AGAINST('France Vion' IN BOOLEAN MODE) as relevance,
MATCH(`from`) AGAINST('France Vion' IN BOOLEAN MODE) as relevance_from
FROM posts
WHERE MATCH(`from`, filter) AGAINST('France Vion' IN BOOLEAN MODE)
ORDER BY (relevance_from * 2) + relevance DESC
此处的第二(2)ORDER BY (relevance_from * 2) + relevance DESC
优先级加倍。在某些情况下,这可能很高。您可以尝试类似1.5
或更低的东西
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句