在MySQL中对多个字段进行加权搜索的最佳方法? [英] Best way to do a weighted search over multiple fields in mysql?

问题描述

这就是我想要做的:

• 将搜索主题与表格中的多个字段进行匹配
• 根据字段的重要性和匹配的相关性(以该顺序)对结果进行排序

例如:假设我有一个博客.然后有人搜索"php".结果将以这种方式出现:

Ex: let's assume I have a blog. Then someone searches for "php". The results would appear that way:

• 首先，根据相关性对字段"title"进行匹配
• 然后，"body"字段的匹配项也按相关性排序
• 以此类推，并带有指定的字段...

我实际上是用PHP中的一个类完成此操作的，但是它使用了很多UNIONS(很多！)，并且随着搜索主题的大小而增长.因此，我担心性能和DOS问题.有人对此有任何线索吗?

I actually did this with a class in PHP but it uses a lot of UNIONS (a lot!) and grows with the size of the search subject. So I'm worried about performance and DOS issues. Does anybody has a clue on this?

推荐答案

这种加权搜索/结果搜索的方法可能适合您:

Probably this approach of doing a weighted search / results is suitable for you:

SELECT *,
    IF(
            `name` LIKE "searchterm%",  20, 
         IF(`name` LIKE "%searchterm%", 10, 0)
      )
      + IF(`description` LIKE "%searchterm%", 5,  0)
      + IF(`url`         LIKE "%searchterm%", 1,  0)
    AS `weight`
FROM `myTable`
WHERE (
    `name` LIKE "%searchterm%" 
    OR `description` LIKE "%searchterm%"
    OR `url`         LIKE "%searchterm%"
)
ORDER BY `weight` DESC
LIMIT 20

它使用选择子查询来提供权重以对结果进行排序.在这种情况下，搜索了三个字段，您可以为每个字段指定权重.它可能比联合会便宜，并且可能只是纯MySQL中最快的方法之一.

It uses a select subquery to provide the weight for ordering the results. In this case three fields searched over, you can specify a weight per field. It's probably less expensive than unions and probably one of the faster ways in plain MySQL only.

如果您有更多数据并且需要更快的结果，则可以考虑使用Sphinx或Lucene之类的东西.

If you've got more data and need results faster, you can consider using something like Sphinx or Lucene.

这篇关于在MySQL中对多个字段进行加权搜索的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！