sql - 为什么我选择 1 需要 40 毫秒，而选择 150 需要 500 秒？

Question

我正在使用 SQLite 3。我有一个表，forums，有 150 行，还有一个表，posts，有大约 440 万行。每个帖子都属于一个论坛。

我想从每个论坛中选择最新帖子的时间戳。如果我使用 SELECT MAX(timestamp) FROM posts WHERE forum_id = 5 请求单个最新帖子，平均需要 40 毫秒。

我要一份所有论坛的列表和他们的最新帖子

SELECT forums.name, max(posts.timestamp)
FROM posts
JOIN forums ON posts.forum_id = forums.id
GROUP BY forums.name

它有效，但它需要 500 秒 -- 超过 12,000 倍的时间，而只选择 150 倍的时间。如果我只是在我的应用程序中编写一个循环来发出 150 个单独的选择查询，它会快得多。

我确实在 posts.timestamp 上创建了一个索引，以及 posts.timestamp, posts.forum_id 的组合索引。它没有帮助。

我做错了什么？

Answer 1

我会创建一个索引

create index ix_posts_forumid_timestamp on posts(forum_id, timestamp)

覆盖 GROUP BY posts.forum_id 查询，如下所示

select forum_id, max(timestamp) 
from posts
group by forum_id 

如果查询在几秒钟内得到处理(应该是)，那么您可以测试加入论坛:

select f.name, t.maxTime
from forums f
(
  select forum_id, max(timestamp) maxTime
  from posts
  group by forum_id 
) t on t.forum_id = f.forum_id

这样的查询也可以被另一个索引覆盖，但是既然你需要所有的论坛，我想它没有第一个索引那么重要。最后，我相信拥有索引的以下查询也应该足够快

select f.name, max(p.timestamp) maxTime
from posts p
join forums f on f.forum_id = p.forum_id
group by p.forum_id