Mysql:执行“不存在".有可能提高性能吗? [英] Mysql: Perform of NOT EXISTS. Is it possible to improve perfomance?

问题描述

我有两个表帖子和评论.表评论具有 post_id 属性.我需要获取所有类型为打开"的帖子，对于这些帖子，没有类型为好"且创建日期为5月1日的评论.

I have two tables posts and comments. Table comments have post_id attribute. I need to get all posts with type "open", for which there are no comments with type "good" and created date MAY 1.

使用这种SQL查询是否最佳:

Is it optimal to use such SQL-query:

SELECT  posts.* FROM  posts  
WHERE NOT EXISTS (
SELECT comments.id FROM comments WHERE comments.post_id = posts.id 
AND  comments.comment_type = 'good' AND 
comments.created_at BETWEEN '2010-05-01 00:00:00' AND '2010-05-01 23:59:59')

在这种情况下，我不确定NOT EXISTS是否是完美的构造.

I'm not sure that NOT EXISTS is perfect construction in this situation.

推荐答案

您是对的-您可以做得更好.参见 Quassnoi 撰写的这篇文章，但结论是:

You are right - you can do better. See this article by Quassnoi for the details but the conclusion is:

这就是为什么在MySQL中搜索缺失值的最佳方法是使用LEFT JOIN/IS NULL或NOT IN而不是NOT EXISTS.

That’s why the best way to search for missing values in MySQL is using a LEFT JOIN / IS NULL or NOT IN rather than NOT EXISTS.

使用NOT IN重写的查询可能如下所示:

Your query rewritten using NOT IN could look like this:

SELECT *
FROM posts  
WHERE posts.id NOT IN (SELECT post_id
                       FROM comments
                       WHERE comments.comment_type = 'good'
                       AND comments.created_at BETWEEN '2010-05-01 00:00:00'
                                                   AND '2010-05-01 23:59:59')

这篇关于Mysql:执行“不存在".有可能提高性能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！