搜索常用术语时，SQL 匹配全文索引速度很慢

Question

简单的 PHP 搜索页面，包含三个字段，查询包含数百万行的 MySQL 数据库。数据由个人资料组成，主要是美国。

当搜索词很少见时（例如“加拿大”），全文索引可将速度提高 9.6 倍。

当搜索词很常见（例如“美国”）时，全文索引会使速度降低 3.6 倍。

有什么方法可以享受这两种技术的好处吗？如果该术语很少见（在不到一半的记录中找到），也许只搜索表的一小部分并构建“MATCH AGAINST”查询，如果该术语很常见，则构建标准“LIKE”查询？这听起来像是一个复杂的解决方案。我想要一个更好的方法。

这是我的 SQL 和搜索时间：

SELECT COUNT(1) FROM `mytable` WHERE `country` LIKE '%canada%' 

1.14 秒（慢）

    SELECT COUNT(1) FROM `mytable` WHERE MATCH(tagline, location, country)
AGAINST(' +canada' IN BOOLEAN MODE) AND country LIKE '%canada%

0.12 秒（快）

SELECT COUNT(1) FROM `mytable` WHERE `country` LIKE '%united states%'

1.09 秒（相对快）

 SELECT COUNT(1) FROM `mytable` WHERE MATCH(tagline, location, country)
AGAINST(' +united +states' IN BOOLEAN MODE) AND country LIKE '%united states%

3.9 秒（慢）

Answer 1

如果您知道搜索是针对国家/地区名称，并且您只需要在 country 列中查找，那么这将比上述任何方法快得多：

... WHERE country = 'canada'

每个查询的 COUNT 是多少？这将有助于解释时间安排。表中有多少行？

同时，您的 FT 查询与您的 LIKE 查询不相同。所以，我不想讨论为什么速度不同。

Answer 2

我的解决方案是生成我自己的停用词列表。我下载了一个数据样本，并使用 Python 找到了最常见的术语。通过 PHP 进行的 MATCH 查询排除了常用术语。在我的数据中，“United”和“States”是常见术语：

$SQL =  "SELECT COUNT(1) FROM `mytable` WHERE MATCH(tagline, location, country)
AGAINST('+sales +united +states' IN BOOLEAN MODE) AND country LIKE '%united states% AND tagline LIKE '%sales%'"

成为：

$SQL =  "SELECT COUNT(1) FROM `mytable` WHERE MATCH(tagline, location, country)
AGAINST('+sales' IN BOOLEAN MODE) AND country LIKE '%united states% AND tagline LIKE '%sales%'"

当所有搜索术语都是“常见”时，MATCH 语句将被完全删除：

$SQL =  "SELECT COUNT(1) FROM `mytable` WHERE MATCH(tagline, location, country)
AGAINST('+united +states' IN BOOLEAN MODE) AND country LIKE '%united states%"

成为：

$SQL =  "SELECT COUNT(1) FROM `mytable` WHERE country LIKE '%united states%"

两全其美。