跨 >1 个表的 MySQL 全文搜索

Question

作为 this question 的更一般情况因为我认为它可能会引起更多人的兴趣...对两个表执行全文搜索的最佳方法是什么？假设有三张表，一张用于程序(带有 submitter_id)，一张用于带有 object_id 的标签和描述:外键引用程序中的记录。我们想要程序的 submitter_id 在其标签或描述中包含特定文本。我们必须使用 MATCH AGAINST，原因我不会在这里详述。不要卡在那个方面。

programs
  id
  submitter_id
tags_programs
  object_id
  text
descriptions_programs
  object_id
  text

以下工作并在 20 毫秒左右的时间内执行:

SELECT p.submitter_id
FROM programs p
WHERE p.id IN
    (SELECT t.object_id
    FROM titles_programs t
    WHERE MATCH (t.text) AGAINST ('china')
UNION ALL
    SELECT d.object_id
    FROM descriptions_programs d
    WHERE MATCH (d.text) AGAINST ('china'))

但我尝试将其重写为 JOIN，如下所示，它运行了很长时间。我必须在 60 秒后杀死它。

SELECT p.id 
FROM descriptions_programs d, tags_programs t, programs p
WHERE (d.object_id=p.id AND MATCH (d.text) AGAINST ('china'))
OR    (t.object_id=p.id AND MATCH (t.text) AGAINST ('china'))

出于好奇，我用 AND 替换了 OR。这也运行了 s 几毫秒，但这不是我需要的。上面的第二个查询有什么问题？我可以接受 UNION 和子选择，但我想了解一下。

Answer 1

在过滤后加入(例如加入结果)，不要尝试加入然后过滤。

原因是您无法使用全文索引。

对评论的回应澄清:我在这里一般使用连接这个词，不是作为 JOIN，而是作为合并或组合的同义词。

我基本上是说您应该使用第一个(更快的)查询或类似的查询。它更快的原因是每个子查询都足够整洁，数据库可以使用该表的全文索引非常快速地进行选择。连接两个(可能更小)结果集(使用 UNION)也很快。这意味着整个过程很快。

慢速版本最终会遍历大量数据以测试它是否是您想要的，而不是快速筛选数据并仅搜索您可能真正需要的行。