替代的MySQL全文搜索语法 [英] Alternative MySQL fulltext search syntax

问题描述

如果您想要相关性，并且还希望按相关性对结果进行排序，则FULLTEXT查询的常见格式为:

If you want the relevance and also the results to be sorted by relevance the common format of a FULLTEXT query is:

选择名称，匹配(名称)再次("Bob")与用户的相关性匹配(名称)再次("Bob")

作为开发人员，我总是喜欢将代码设为DRY(不要重复自己).是否有任何理由不将查询写为:

As a developer I always like to make my code DRY(don't repeat yourself). Is there any reason not to write the query as:

选择名称，MATCH(name)AGAINST('Bob')AS相关性来自具有相关性的用户>0 ORDER BY关联性DESC

似乎返回相同的结果，但是我应该担心ORDER BY导致查询变慢吗?这些查询是否等效?

It seems to return the same results but should I be worried about the ORDER BY causing the query to be slower? Are these queries equivalent?

两次指定 MATCH()不会降低MySQL手册中提到的性能.

Specifying MATCH() twice does not decrease performance as noted in the MySQL manual.

自然语言全文搜索

要获得此结果，您应该两次指定 MATCH():一次在 SELECT 列表中，然后在 WHERE 中条款.这不会引起额外的开销，因为MySQL优化器注意两个 MATCH()调用是相同并调用全文仅搜索一次代码.

To achieve this result, you should specify MATCH() twice: once in the SELECT list and once in the WHERE clause. This causes no additional overhead, because the MySQL optimizer notices that the two MATCH() calls are identical and invokes the full-text search code only once.

推荐答案

不幸的是，根据

Unfortunately, according to the MySQL SELECT documentation, "the HAVING clause is applied nearly last, just before items are sent to the client, with no optimization."

区别在于，第一个查询将使用全文索引来计算名称中具有"Bob"的行的仅相关性".第二个查询将计算 all 行的相关性，然后丢弃其中的大多数行(可能是在对整个表进行排序之后).因此，第二个查询要慢得多.即使将ORDER BY子句放在第一个查询上，它仍然比使用HAVING更快:

The difference is that the first query will use the fulltext index to calculate the relevance only for rows that have 'Bob' in name. The second query will calculate the relevance for all rows, then throw out most of them (possibly after sorting the entire table). Therefore, the second query is significantly slower. Even if you put the ORDER BY clause onto the first query, it will still be faster than using HAVING:

SELECT name, MATCH(name) AGAINST('Bob') AS relevance
FROM users
WHERE MATCH(name) AGAINST('Bob')
ORDER BY relevance DESC

通常，不要对应该在WHERE子句中的项目使用HAVING."

In general, "do not use HAVING for items that should be in the WHERE clause."

这篇关于替代的MySQL全文搜索语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！