对mysql中的多个字段进行加权搜索的最佳方法?

2021-12-20 00:00:00 search php mysql

这是我想要做的:

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

例如:假设我有一个博客.然后有人搜索php".结果会这样显示:

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

  • 首先，匹配字段title"，按相关性排序
  • 然后，字段body"的匹配项也按相关性排序
  • 等等与指定的字段...

我实际上是用 PHP 中的一个类完成的，但它使用了很多联合(很多！)并且随着搜索主题的大小而增长.所以我担心性能和 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.

相关文章