MySQL对包含关键字的数据进行最佳匹配

Question

我的表中有两个name列。(name1 name2)

我希望接收关键字作为输入，并在包含关键字的数据中以最相似的顺序输出。

如果用户输入ed，我们希望输出的顺序是'ed'， 'ed Sheeran'和'Ahmedzidan'。("Ed Sheeran"one_answers"Ahmed Zidan"的顺序可能因匹配方法而异。)

我们希望单词'ed'是最相似的，并且紧接在单词'ed'后面。

我不知道如何精确匹配。

即使上面的'ed'包含在name1或name2中，也会被搜索。两者之间没有优先级。

我现在使用的方法:

select
((LENGTH(name1) - LENGTH(( 'ed')))) + ((LENGTH(name) - LENGTH(( 'ed')))
) as score
from user
where name like '%ed%' or name2 like '%ed%'
order by score asc

的另一种方法:

select
(CASE WHEN name1 = 'ed' or name2 = 'ed' THEN 4
WHEN name1 like 'ed%' or name2 like 'ed%' THEN 3
WHEN name1 like '%ed' or name2 like '%ed' THEN 2
WHEN name1 like '%ed%' or name2 like '%ed%' THEN 1
END
)
as score
from user
where name like '%ed%' or name2 like '%ed%'
order by score desc

但是，两个结果都和我想的不一样，我不知道哪个更快。

我尝试使用full-text index，但似乎需要太多的牺牲(?)来搜索一个字母表。当用户在长单词中输入关键字时，它太慢了。

示例:keyword: ed ->0.2秒，关键词:艾德·希兰;5 s .

最好的方法是什么?

如果以上两种方法是最好的，哪一种更快?

Answer 1

让我来讨论一下查询的每个部分对性能的影响:

1. WHERE有OR和LIKE，前面有一个通配符。每一个都强制查询做一个完整的扫描，检查每一行。
2. 我不需要进一步讨论;所有其他方面(包括冗长的CASE)在判断速度时都不那么重要。像POSITION和替代品这样的东西可能会减少1%。

如果表很大(并且不能缓存在RAM中)，那么这将会有所帮助:INDEX(name1, name2)这里的技巧是将表扫描更改为索引扫描。

所有工作都在"buffer_pool"在RAM中。当表大于RAM，并且查询需要查看所有行时，处理必须从缓冲池中取出数据，以便从磁盘加载数据。I/O可能是影响性能的最大因素。

表的b树包含所有行的所有列。上面提到的INDEX为每个name1、name2和组成PRIMARY KEY的任何列都包含一行。也就是说，索引可能比表小。因此，索引可能位于RAM中，而数据必须是页入的。(这是关于I/o)

Answer 2

我认为你可以使用POSITION函数并按它排序。不需要使用select CASE，因为在您的查询和复制LIKE函数中没有这样的逻辑操作，这太浪费时间了。使用position函数，你可以得到你想要的结果，如果你只是想订购&;第一，其次是其他的"ed"像"先生。Bambang Ed" .

SELECT name, POSITION('a' IN name) pos FROM user WHERE name LIKE '%a%' ORDER BY pos ASC