方法:对搜索结果进行排名 [英] How-to: Ranking Search Results

问题描述

我遇到了一个Webapp开发问题，为此我开发了一个解决方案，但是我试图找到其他可能解决我所遇到的性能问题的想法.

I have a webapp development problem that I've developed one solution for, but am trying to find other ideas that might get around some performance issues I'm seeing.

问题陈述:

• 用户输入多个关键字/令牌
• 应用程序搜索与令牌匹配的内容
• 每个令牌需要一个结果
  • 即，如果一个条目具有3个令牌，则我需要3个条目id
  • a user enters several keywords/tokens
  • the application searches for matches to the tokens
  • need one result for each token
    • ie, if an entry has 3 tokens, i need the entry id 3 times
    • 为令牌匹配分配X点
    • 根据得分对条目ID进行排序
    • 如果点值相同，请使用日期对结果进行排序

    我想做的但还没有弄清楚的是，发送1个查询，该查询返回类似于in()结果的内容，但为每个令牌匹配的每个令牌返回一个重复的条目ID已选中.

    What I want to be able to do, but have not figured out, is to send 1 query that returns something akin to the results of an in(), but returns a duplicate entry id for each token matches for each entry id checked.

    是否有比我正在做的更好的方法，即使用多个单个查询每个令牌运行一个查询?如果是这样，实现这些目的最简单的方法是什么?

    Is there a better way to do this than what I'm doing, of using multiple, individual queries running one query per token? If so, what's the easiest way to implement those?

    修改
    我已经标记了条目，例如，"see spot run"的条目ID为1，并且三个标记"see"，"spot"，"run"位于单独的标记表中，以及与之相关的条目ID，因此该表可能如下所示:

    edit
    I've already tokenized the entries, so, for example, "see spot run" has an entry id of 1, and three tokens, 'see', 'spot', 'run', and those are in a separate token table, with entry ids relevant to them so the table might look like this:

    'see', 1 
    'spot', 1 
    'run', 1 
    'run', 2 
    'spot', 3 
    

    推荐答案

    您可以在MySQL中使用"UNION ALL"在一个查询中实现这一点.

    you could achive this in one query using 'UNION ALL' in MySQL.

    只需遍历PHP中的令牌，即可为每个令牌创建UNION ALL:

    Just loop through the tokens in PHP creating a UNION ALL for each token:

    例如，如果标记是"x"，"y"和"z"，则您的查询可能看起来像这样

    e.g if the tokens are 'x', 'y' and 'z' your query may look something like this

    SELECT * FROM `entries` 
    WHERE token like "%x%" union all 
        SELECT * FROM `entries` 
        WHERE token like "%y%" union all 
            SELECT * FROM `entries` 
            WHERE token like "%z%" ORDER BY score ect...
    

    order子句应作为一个整体在整个结果集上起作用，这就是您所需要的.

    The order clause should operate on the entire result set as one, which is what you need.

    就性能而言，它并不会那么快(我猜是这样)，但是对于数据库，从速度方面来说，主要的开销通常是从PHP向数据库引擎发送查询并接收结果.借助这种技术，每个令牌只会发生一次，而不是一次，因此性能会提高，我只是不知道这样做是否足够.

    In terms of performance it won't be all that fast (I'm guessing), however with databases the main overhead in terms of speed is often sending the query to the database engine from PHP and receiving the results. With this technique this only happens once instead of once per token, so performance will increase, I just don't know if it'll be enough.

    这篇关于方法:对搜索结果进行排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！