mysql搜索where子句，每个条件都有不同的分数

Question

In Sum

我有两列，名字和name_searchable。我想在这些列中搜索(它们有数百万行)，并根据它们的匹配率返回结果。我有两个重要的标准:搜索应该是高效和快速的。我怎样才能做到这一点？

In Details

我打算要一张有几百万行的桌子。因此，基本上，我创建了一个转储表来测试有一百万行的查询。该表使用的是MyISAM存储引擎，它的索引和主键是id号。我想要进行的搜索涉及name字段，它是一个varchar列。现在，基于一个查询，我想返回与查询部分或全部匹配的所有结果。因此，当用户搜索“bj rn borg”时，我想返回这两种内容：

• bj rn borg
• bj rn borgus
• bjorn borg(注意到o)

等等..。

这里的重要因素是，=运算符应该总是返回比LIKE运算符更高的排序。因此，“bj rn borg”应该总是出现在“bjorn borgus”之前。

最近，我问了一个问题，如何返回结果的对话，不敏感的模式，但不幸的是，我无法使它工作。因此，我在name列中创建了另一列，该列仅以英文字符存储名称。所以我们有name和name_searchable字段。

嗯，我用一个存储过程尝试了整个过程，但是与普通的查询相比，它显然非常慢。因此，我想知道我是否可以根据哪个子句匹配的结果来排序。换言之：

SELECT * FROM myUsers WHERE name = 'björn borg' OR name_searchable = 'bjorn borg' OR name LIKE '%björn borg%' OR name_searchable LIKE '%bjorn borg%'; 

所以基本上，我们的想法是给每个条件不同的分数。我的意思是，虽然名字=‘bj rn borg’应该有等级，比如说，5，name_searchable像'%bjorn borg%‘应该有2(第二个4分，第三个3分.)如何使用MySql使其工作？(对我来说，效率和速度是重要的因素)

Answer 1

如果不执行LIKE '%<text>%'，您将获得更好的性能，因为这将不能正确地使用索引，而应该使用LIKE '<text>%'。我建议您考虑一下，如果您希望用户能够搜索name_searchable LIKE '%s%'和相关的性能，当查询花费很长的时间并返回太多的结果时。

你试过吗

SELECT CASE WHEN name = 'björn borg' THEN 1 
  WHEN name_searchable = 'bjorn borg' THEN 2 
  WHEN name LIKE '%björn borg%' THEN 3 
  WHEN name_searchable LIKE '%bjorn borg%' THEN 4 ELSE 5 END AS rank, * 
FROM myUsers 
WHERE name = 'björn borg' 
  OR name_searchable = 'bjorn borg' 
  OR name LIKE '%björn borg%' 
  OR name_searchable LIKE '%bjorn borg%'
ORDER BY CASE WHEN name = 'björn borg' THEN 1 
  WHEN name_searchable = 'bjorn borg' THEN 2 
  WHEN name LIKE '%björn borg%' THEN 3 
  WHEN name_searchable LIKE '%bjorn borg%' THEN 4 ELSE 5 END

当然，最快的方法是添加一个LIMIT 1

另一种选择是，只有在完全匹配失败时才使用类似的搜索：

SELECT CASE WHEN name = 'björn borg' THEN 1 
  WHEN name_searchable = 'bjorn borg' THEN 2 
  WHEN name LIKE '%björn borg%' THEN 3 
  WHEN name_searchable LIKE '%bjorn borg%' THEN 4 ELSE 5 END AS rank, * 
FROM myUsers 
WHERE name = 'björn borg' 
  OR name_searchable = 'bjorn borg' 
  OR (
    NOT EXISTS (SELECT TOP 1 1 FROM myUsers WHERE name = 'björn borg' OR name_searchable = 'bjorn borg' )
    AND (
    OR name LIKE '%björn borg%' 
    OR name_searchable LIKE '%bjorn borg%'
    )
  )
ORDER BY CASE WHEN name = 'björn borg' THEN 1 
  WHEN name_searchable = 'bjorn borg' THEN 2 
  WHEN name LIKE '%björn borg%' THEN 3 
  WHEN name_searchable LIKE '%bjorn borg%' THEN 4 ELSE 5 END

Answer 2

您考虑过将查询分离出来并进行UNION处理吗？

SELECT 5 AS rank, * FROM myUsers WHERE name = 'björn borg' UNION
SELECT 4 AS rank, * FROM myUsers WHERE name_searchable = 'bjorn borg' UNION
SELECT 3 AS rank, * FROM myUsers WHERE name LIKE '%björn borg%' UNION
SELECT 2 AS rank, * FROM myUsers WHERE name_searchable LIKE '%bjorn borg%'
ORDER BY 1 DESC