Saw some major performance differences between NOT IN and NOT EXISTS,
Was wondering, out of interest, if it would be possible to optimise this query?
-- (ignore the SELECT *)
SELECT *
FROM `comments`
WHERE comment_id NOT IN (65,64,63)
ORDER BY `comment_id` DESC
Assuming that comment_id is the primary key for the table comments, I don't think this can be improved upon. I don't see why you would want to try, either. Your condition is on the primary key, so the query should use the index.
MySQL puts all its data "behind" the primary key. Or, as the documentation puts it:
Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key.
(...)
- If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
So if your query uses the primary key, that is the fastest way to retrieve your data.
What does the explain plan say?
And why do you feel the need to try to optimise at all?
The difference in performance between NOT IN and NOT EXISTS is only when using a sub-select to "fill" the IN-list.
Compare
SELECT c.*
FROM comments c
WHERE c.author_id NOT IN (
SELECT a.author_id
FROM authors a
WHERE a.first_name = 'Jay'
);
with
SELECT c.*
FROM comments c
WHERE NOT EXISTS (
SELECT *
FROM authors a
WHERE c.author_id = a.author_id
AND a.first_name = 'Jay'
);
although I would use
SELECT c.*
FROM comments c
LEFT JOIN authors a
ON (c.author_id = a.author_id AND a.first_name = 'Jay')
WHERE a.author_id IS NULL;
