mysql - 从 H2 中的多对多关系中高效选择

Question

我正在使用 H2，并且我有一个书籍(表 Entries)和作者(表 Persons)的数据库，通过多对多关系连接，本身存储在表 Authorship 中。该数据库相当大(900'000 多人和 250 万多本书)。

我正在尝试有效地选择由至少一位姓名与模式(如“%pattern%”)匹配的作者创作的所有书籍的列表。这里的技巧是，该模式应该严格限制匹配作者的数量，并且每个作者拥有相当少量的相关书籍。

我尝试了两个查询:

SELECT p.*, e.title FROM (SELECT * FROM Persons WHERE name LIKE '%pattern%')  AS p
  INNER JOIN Authorship AS au ON au.authorId = p.id
  INNER JOIN Entries AS e ON e.id = au.entryId;

和:

SELECT p.*, e.title FROM Persons AS p
  INNER JOIN Authorship AS au ON au.authorId = p.id
  INNER JOIN Entries AS e ON e.id = au.entryId
WHERE p.name like '%pattern%';

我预计第一个会快得多，因为我正在加入一个更小的作者(子)表，但是它们都需要很长时间。事实上，我可以手动将查询分解为三个选择，并更快地找到我想要的结果。

当我尝试解释查询时，我发现它们确实非常相似(表上的完整联接，然后只有 WHERE 子句)，所以我的问题是:如何实现快速选择，这依赖于事实上，对作者的过滤器应该会导致与其他两个表的连接小得多？

请注意，我使用 MySQL 尝试了相同的查询，得到的结果与我的预期一致(选择第一个要快得多)。

谢谢。

Answer 1

好的，这终于对我有用了。

而不是运行查询:

SELECT p.*, e.title FROM (SELECT * FROM Persons WHERE name LIKE '%pattern%') AS p
  INNER JOIN Authorship AS au ON au.authorId = p.id
  INNER JOIN Entries AS e ON e.id = au.entryId;

...我跑了:

SELECT title FROM Entries e WHERE id IN (
  SELECT entryId FROM Authorship WHERE authorId IN (
    SELECT id FROM Persons WHERE name LIKE '%pattern%'
  )
)        

这不是完全相同相同的查询，因为现在我没有将作者 ID 作为结果中的一列，但这实现了我想要的:利用模式限制的事实将作者数量设置为非常小的值，以便仅通过少量条目进行搜索。

有趣的是，这在 H2 中效果很好(比连接快得多)，但在 MySQL 中却非常慢。 (这与 LIKE '%pattern%' 部分无关，请参阅其他答案中的评论。)我认为查询的优化方式有所不同。