duanchi0897
2013-08-14 01:37
浏览 79
已采纳

搜索多个值,按相关性排序(php + MySQL,无全文搜索)

I have a table with title and keywords columns that I'd like to search on. I want to add the ability to use multi-term search, but then it should definitely sort the results by relevance. FullTextSearch is not an option because I'm currently using MySQL5.5 and innoDB.

My current approach is as follows:

  1. I search for every term individually and store matching ids in a php array.
  2. Once I've searched for all terms I count id occurrences and sort accordingly.

What I'm wondering right now is if there's a more efficient way to do it as I'm primarily using php for this. Should I do some of this processing in the database? Should I change my approach drastically?

The table is relatively small (less than 10k records) and I don't expect it to become much larger in the near future.

Any suggestions? Thanks.

图片转代码服务由CSDN问答提供 功能建议

我有一个表格,其中包含我要搜索的标题和关键字列。 我想添加使用多项搜索的功能,但是它应该通过相关性对结果进行排序。 FullTextSearch不是一个选项,因为我目前正在使用MySQL5.5和innoDB。

我目前的方法如下:

  1. 我单独搜索每个术语并在php数组中存储匹配的ID。
  2. 一旦我搜索了所有术语,我就会计算id的出现次数并进行相应的排序。

    我现在想知道的是,如果有一种更有效的方式来做这件事,因为我主要使用PHP来实现这一点。 我应该在数据库中进行一些处理吗? 我是否应该彻底改变我的方法?

    该表格相对较小(不到1万条记录),我预计它不会在不久的将来变得更大。 \ n

    有什么建议吗? 谢谢。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douxia2053 2013-08-14 03:14
    已采纳

    You can do the count in SQL. Here is an example:

    select ((col like 'term1') +
            (col like 'term2') +
             . . .
            (col like 'termN')
           ) as NumMatches
    from t
    having NumMatches > 0
    order by NumMatches desc;
    

    MySQL treats booleans as 0 (for false) and 1 (for true). You can add them together to get the total number of matches.

    点赞 评论

相关推荐 更多相似问题