mysql - 内连接关键字搜索的SQL数据库索引设计

Question

我有这个问题

SELECT a.* 
FROM entries a 
INNER JOIN entries_keywords b ON a.id = b.entry_id 
INNER JOIN keywords c ON b.keyword_id = c.id 
WHERE c.key IN ('wake', 'up') 
GROUP BY a.id 
HAVING COUNT(*) = 2

但是速度很慢。如何优化索引以加快速度？

编辑这是当前模式

CREATE TABLE `entries` (`id` integer PRIMARY KEY AUTOINCREMENT, `sha` text);
CREATE TABLE `entries_keywords` (`id` integer PRIMARY KEY AUTOINCREMENT, `entry_id` integer REFERENCES `entries`, `keyword_id` integer REFERENCES `keywords`);
CREATE TABLE `keywords` (`id` integer PRIMARY KEY AUTOINCREMENT, `key` string);
CREATE INDEX `entries_keywords_entry_id_index` ON `entries_keywords` (`entry_id`);
CREATE INDEX `entries_keywords_entry_id_keyword_id_index` ON `entries_keywords` (`entry_id`, `keyword_id`);
CREATE INDEX `entries_keywords_keyword_id_index` ON `entries_keywords` (`keyword_id`);
CREATE INDEX `keywords_key_index` ON `keywords` (`key`);

我正在使用 Sqlite3，查询没有失败，但是很慢。

现在我是这样的查询(每个关键字的子查询):

select *
from (
    select *
    from (entries) e
    inner join entries_keywords ek on e.id = ek.entry_id
    inner join keywords k on ek.keyword_id = k.id
    where k.key = 'wake') e
inner join entries_keywords ek on e.id = ek.entry_id
inner join keywords k on ek.keyword_id = k.id
where k.key = 'up';

这要快得多，但感觉不对，因为如果我有很多关键字，它会变得很丑。

Answer 1

该查询所需的关键索引

• 关键字(关键)
• entries_keywords(keyword_id,entry_id)
• 条目(id)

您必须使用 MySQL，否则 SELECT a.* 会失败。
EDIT 在关于此声明的第 2 条评论之后，让我指出为什么 select a.* 会在这里失败 - 这是因为 分组依据。

说明一下，因为criteria(WHERE)在c.key上，所以需要索引
这然后上升到 JOIN 对 b.keyword_id。我们创建一个包含 b.entry_id 的索引，这样它就不必查找表 - 索引本身就可以覆盖所需的列。
最后，a.id=b.entry_id 连接回条目表，因此我们索引该表的 id。

entries(id) 很可能已经是主键，但您可能已经将 entries_keywords 索引为 其他方式 - 它将无法满足此连接。