CODEWITHSUNDEEP
mysql
Mayor of the Plattenbaus
Mayor of the Plattenbaus

Reputation: 1256

How to speed up a NOT IN query?

I have the following query:

SELECT images.id from images WHERE images.id NOT IN
(
    SELECT
        temp.target_id

    FROM (
        SELECT
            images_user_groups.images_id AS target_id,
            images_user_groups.user_groups_id AS source_id

        FROM
            images_user_groups
    ) AS temp

        INNER JOIN
        user_groups ON user_groups.id = temp.source_id
    INNER JOIN
        images ON images.id = temp.target_id
    WHERE
        user_groups.description LIKE "%Freigabe ins Presseportal%"
    GROUP BY
        temp.target_id
)

It runs, but it goes very slowly -- 5 minutes and counting as I write this. (The subquery that I'm running a NOT IN on returns 36,000 rows, and I assume that the parent query is taking time to check every one of the 38,000 entries in the images table against those 36,000 rows.)

Is there a way I can speed this query up?

Upvotes: 1

Views: 81

Answers (2)

Nick
Nick

Reputation: 147146

It should be a lot faster to write this as a LEFT JOIN, looking for NULL values in user_groups to indicate that the image was not in that group:

SELECT i.id 
FROM images i
JOIN images_user_groups iu ON iu.images_id = i.id
LEFT JOIN user_groups u ON u.id = iu.user_groups_id AND u.description LIKE "%Freigabe ins Presseportal%"
GROUP BY i.id
HAVING COUNT(u.id) = 0

The problem with NOT IN and NOT EXISTS is that the WHERE clause has to be evaluated for every row in images.

Upvotes: 3

Fahmi
Fahmi

Reputation: 37473

You can try in below way

SELECT images.id from images WHERE images.id NOT IN
(
    SELECT images_user_groups.images_id 
    FROM images_user_groups INNER JOIN user_groups ON user_groups.id = images_user_groups.user_groups_id
    inner join images ON images.id = images_user_groups.images_id
    WHERE user_groups.description LIKE "%Freigabe ins Presseportal%"
)A

Upvotes: 0

Related Questions