問題描述

這是我想要做的:

• 將搜索主題與表格的多個字段進行匹配
• 按字段的重要性和匹配的相關性(按該順序)對結果進行排序

例如:假設我有一個博客.然后有人搜索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.

這篇關于對mysql中的多個字段進行加權搜索的最佳方法?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！