Mysql: Perform of NOT EXISTS. Is it possible to improve perfomance?

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.

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')

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

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.

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')

Method 2

No idea whether it is faster, you could check it out:

SELECT * FROM posts
LEFT JOIN comments 
ON comment.postid = post.id
AND comment.comment_type='good'
WHERE comment.postid IS NULL

Assuming postid is never NULL / a non NULLable column.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0