我有一个查询，它有时运行得非常快，有时运行得非常慢，这取决于查询中匹配全文布尔搜索的结果数。
查询还包含一个子查询。
没有子查询，主查询总是快速的。
子查询本身也总是很快的。
但他们在一起很慢。
从where子句中删除全文搜索，而改为按全文搜索排序，速度非常快。
所以当在where中使用全文搜索时，它的速度很慢。
下面是简单易读的概述，具体查询如下。
我已经在底部包含了这个模式，但是如果没有我的数据集很难复制，不幸的是我不能共享数据集。
我在示例查询中包含了计数和增量，以给出所涉及数据大小的一些指示。
实际上，我有一个解决方案，只需接受包含不相关数据的结果，然后在php中过滤掉这些数据。但是我想了解为什么我的查询执行得很差，以及如何在mysql中解决这个问题。
特别是，我搞不懂为什么全文搜索的顺序是“按”，而不是“在哪里”。

我想要的查询（慢）

我有一个类似这样的查询：

select 
  *, 
  MATCH (name) AGAINST ('Old Tra*' IN BOOLEAN MODE) AS relevance_score 
from 
  `app_records` 
where 
  `id` in (
    select 
      distinct(app_record_parents.record_id) 
    from 
      `app_group_records` 
      inner join `app_record_parents`
        on `app_record_parents`.`parent_id` = `app_group_records`.`record_id` 
    where 
      `group_id` = 3
  ) 
  and
    MATCH (name) AGAINST ('Old Tra*' IN BOOLEAN MODE)
order by 
  `relevance_score` desc 
limit 
  10;

此查询需要10秒钟。
这对于这种查询来说太长了，我需要看毫秒。
但是这两个查询单独运行时运行得非常快。

子对象将自行选择

select distinct(app_record_parents.record_id) 
from
   `app_group_records` 
   inner join
      `app_record_parents` 
      on `app_record_parents`.`parent_id` = `app_group_records`.`record_id` 
where
   `group_id` = 3

子选择本身需要7毫秒，有2600个结果。

不带子选择的主查询

select 
  *, 
  MATCH (name) AGAINST ('Old Tra*' IN BOOLEAN MODE) AS relevance_score 
from 
  `app_records` 
where 
  MATCH (name) AGAINST ('Old Tra*' IN BOOLEAN MODE)
order by 
  `relevance_score` desc 
limit 
  10;

没有子选择的主查询需要6毫秒，可能有2971个结果（显然有10个限制）。

速度更快，结果更少

同样的查询，但是匹配“oldtraf”而不是“oldtra”需要300毫秒。
当使用“老特拉夫”和“老特拉夫”时，结果的数目明显不同。

完整查询结果

“旧茶”：9
“老特拉福德”：2

与全文搜索匹配的记录

“旧茶”：2971
“老特拉福德”：120

删除where可以解决问题

删除where并返回按相关性分数排序的所有记录非常快，仍然给了我想要的体验：

select 
  *, 
  MATCH (name) AGAINST ('Old Tra*' IN BOOLEAN MODE) AS relevance_score 
from 
  `app_records` 
where 
  `id` in (
    select 
      distinct(app_record_parents.record_id) 
    from 
      `app_group_records` 
      inner join `app_record_parents`
        on `app_record_parents`.`parent_id` = `app_group_records`.`record_id` 
    where 
      `group_id` = 3
  )
order by 
  `relevance_score` desc 
limit 
  10;

但是我需要在代码中过滤掉不相关的结果

我在php中使用它，所以我现在可以过滤我的结果来删除任何相关性得分为0的结果（例如，如果只有2个匹配项，那么仍然会包含8个相关性得分为0的随机结果，因为我没有使用where）。

array_filter($results, function($result) {
    return $result->relevance_score > 0;
});

显然，这是非常快，所以这不是一个真正的问题。

但我还是不明白我的问题出在哪里。

因此，我有一个修复如上所述。但我还是不明白为什么我的查询速度慢。
很明显，从全文搜索中可能得到的结果的数量造成了一个问题，但究竟为什么以及如何绕过这个问题是我无法理解的。

表架构

这是我的table

CREATE TABLE `app_records` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `app_models_name_IDX` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=960004 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `app_record_parents` (
  `record_id` int(10) unsigned NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  KEY `app_record_parents_record_id_IDX` (`record_id`) USING BTREE,
  KEY `app_record_parents_parent_id_IDX` (`parent_id`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `app_group_records` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(10) unsigned NOT NULL,
  `record_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

关于查询正在执行的操作的注解

子查询正在获取属于组\u id 3的记录\u id的列表。
所以，虽然app\u记录中有960004条记录，但只有2600条属于第3组，正是针对这2600条记录，我试图查询与“old tra”匹配的名称，
所以子查询得到了2600个记录id的列表，然后我做了一个 WHERE id IN <subquery> 从应用程序记录中获取相关结果。

编辑：使用联接同样慢

仅仅添加使用联接也有同样的问题。“old tra”需要10秒，“old traf”需要400毫秒，在where中不使用全文搜索时速度非常快。

SELECT 
  app_records.*, 
  MATCH (NAME) AGAINST ('Old Tra*' IN BOOLEAN MODE) AS relevance_score 
FROM 
  `app_records` 
  INNER JOIN app_record_parents ON app_records.id = app_record_parents.record_id 
  INNER JOIN app_group_records ON app_group_records.record_id = app_record_parents.parent_id 
WHERE 
  `group_id` = 3 
  AND MATCH (NAME) AGAINST ('Old Tra*' IN BOOLEAN MODE) 
GROUP BY 
  app_records.id 
LIMIT 
  10;