mysql - 当用户有很多评论时，为什么这个简单的 SELECT 需要 6 秒？

Question

当有人在一篇文章中发表评论时，我有这个选择来向用户显示一些通知。我注意到，帖子中包含很多评论的用户可能需要 6 秒以上的时间。

select 'comments' prefix, c.foto, c.data as data, c.user,
       concat(k.user, ' comments your post') as logs
from comments c  

inner join posts p on c.foto = p.id 
inner join cadastro k on c.user = k.id 

where p.user = 1 and c.user <> 1 and c.delete = 0
order by c.data desc
limit 5

我想向用户显示通知，有人评论你的帖子，为此，我在帖子上使用了 inned join(以了解评论是否来自用户“1”)和 inner join cadastro(以获取用户昵称- 评论用户 1 帖子的用户)。

检查用户是否为 1 的位置，c.user <> 1(不显示他自己的评论通知)和 c.delete(评论未删除)。

我的表:

`posts` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user` int(11) UNSIGNED NOT NULL,
  `foto` varchar(400),
  `data` datetime NOT NULL,
  `delete` tinyint(1) NOT NULL DEFAULT '0',
  FOREIGN KEY (`user`) REFERENCES cadastro (`id`),
  PRIMARY KEY (`id`)
)

`comments` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `foto` int(11) UNSIGNED NOT NULL,
  `user` int(11) UNSIGNED NOT NULL,
  `texto` varchar(3000) NOT NULL,
  `data` datetime NOT NULL,
  `delete` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `foto_delete` (foto, `delete`),
  FOREIGN KEY (`foto`) REFERENCES posts (`id`) ON DELETE CASCADE
)

当用户有大约 200.000 条评论时，为什么要花这么长时间，有什么想法吗？ (如果用户有 1000 个，则速度很快)。

Answer 1

如果没有索引，为了运行您的查询，引擎通常会扫描所有行以在 ON、WHERE 以及 ORDER BY 子句中查找所需的值。

您可以做的一件简单的事情就是创建索引:

CREATE INDEX cadastro_id ON cadastro(id);
CREATE INDEX posts_id ON posts(id);

CREATE INDEX posts_user ON posts(user);

CREATE INDEX comments_foto ON comments(foto);
CREATE INDEX comments_user ON comments(user);
CREATE INDEX comments_delete ON comments(delete);

CREATE INDEX comments_data ON comments(data);

测量当前花费的时间，然后应用这些 Index 并再次测量，并在此处告诉。

另见:
https://dev.mysql.com/doc/refman/5.7/en/create-index.html
https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html