MySQL match() against() - 按相关性和列排序? [英] MySQL match() against() - order by relevance and column?

问题描述

好的，所以我尝试在多列中进行全文搜索，就像这样简单:

Okay, so I'm trying to make a full text search in multiple columns, something simple like this:

SELECT * FROM pages WHERE MATCH(head, body) AGAINST('some words' IN BOOLEAN MODE)

现在我想按相关性排序，(找到多少个词?)我已经能够用这样的东西来做:

Now i want to order by relevance, (how many of the words are found?) which I have been able to do with something like this:

SELECT * , MATCH (head, body) AGAINST ('some words' IN BOOLEAN MODE) AS relevance 
FROM pages
WHERE MATCH (head, body) AGAINST ('some words' IN BOOLEAN MODE)
ORDER BY relevance

现在我迷路的部分来了，我想优先考虑head列中的相关性.

Now here comes the part where I get lost, I want to prioritize the relevance in the head column.

我想我可以创建两个相关性列，一个用于 head，另一个用于 body，但那时我会在表格中进行一些相同的搜索三次，对于我正在制作的这个函数，性能很重要，因为查询将被连接并与其他表匹配.

I guess I could make two relevance columns, one for head and one for body, but at that point I'd be doing somewhat the same search in the table three times, and for what i'm making this function, performance is important, since the query will both be joined and matched against other tables.

那么，我的主要问题是，是否有一种更快的方法来搜索相关性并确定某些列的优先级?(作为奖励，甚至可能使相关性计算列中单词出现的次数?)

So, my main question is, is there a faster way to search for relevance and prioritize certain columns? (And as a bonus possibly even making relevance count number of times the words occur in the columns?)

任何建议或建议都会很棒.

Any suggestions or advice would be great.

注意:我将在 LAMP 服务器上运行它.(本地测试中的 WAMP)

Note: I will be running this on a LAMP-server. (WAMP in local testing)

推荐答案

这可能增加与您想要的头部的相关性.它不会翻倍，但对您来说可能已经足够了:

This might give the increased relevance to the head part that you want. It won't double it, but it might possibly good enough for your sake:

SELECT pages.*,
       MATCH (head, body) AGAINST ('some words') AS relevance,
       MATCH (head) AGAINST ('some words') AS title_relevance
FROM pages
WHERE MATCH (head, body) AGAINST ('some words')
ORDER BY title_relevance DESC, relevance DESC

-- alternatively:
ORDER BY title_relevance + relevance DESC

如果您可以灵活地切换数据库引擎，您还想研究的替代方法是 Postgres.它允许设置运营商的权重并调整排名.

An alternative that you also want to investigate, if you've the flexibility to switch DB engine, is Postgres. It allows to set the weight of operators and to play around with the ranking.

这篇关于MySQL match() against() - 按相关性和列排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！