操作方法:对搜索结果进行排名

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
  • 为令牌匹配分配 X 点
  • 根据点对条目 id 进行排序
  • 如果点值相同，则使用日期对结果进行排序

  我希望能够做但还没有弄清楚的是发送 1 个查询，该查询返回类似于 in() 结果的内容，但为每个条目 id 的每个令牌匹配返回一个重复的条目 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.