MySQL匹配（）反对（） - 按相关性和列排序？ [英] MySQL match() against() - order by relevance and column?

问题描述

好的，所以我试图在多列中进行全文搜索，例如这样简单：

  SELECT * FROM pages WHERE MATCH（head，body）AGAINST（'some words'in BOOLEAN MODE）
  

现在我想通过相关性排序（可以找到多少个单词？），我可以用类似的方法做到这一点：

  SELECT *，MATCH（head，body）AGAINST（在BOOLEAN模式中的'some words'）相关性
 FROM页面
匹配（head，body）反对（'some words'IN BOOLEAN MODE ）
 ORDER BY相关性
  

现在我来了迷失的部分，我想优先考虑头部列中的相关性。

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

所以，我的主要问题是，有没有更快的方法搜索相关性并确定某些列的优先顺序？ （作为一个奖金，甚至可能使相关次数出现在单词出现的次数上）？

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

注意：我将在LAMP服务器上运行此操作。 （WAMP在本地测试）

解决方案

这个可能想。它不会加倍，但它可能足以满足你的需要：

 选择页面*，
 MATCH（head，body）AGAINST（'some words'）AS相关性，
 MATCH（head）反对（'some words'）AS title_relevance 
 FROM pages 
匹配（头部，身体）反对（'某些单词'）
 ORDER BY title_relevance DESC，相关性DESC 
 
  - 或者：
 ORDER BY title_relevance +相关性DESC 
  

如果您有切换数据库引擎的灵活性，您还想调查的另一种方法是 Postgres 。它允许设置运营商的权重，并与排名玩弄。

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

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

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.

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

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匹配（）反对（） - 按相关性和列排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！