rewriting query to avoid not in and boost performance

Question

I wanted to select all pictures that do not have a style associated with it. I have a style table that maps the style with its picture. Here's the query that I have now:

SELECT picture.id, picture.caption, picture.lowresimageurl, picture.medresimageurl
                    FROM instagram_shop_picture picture 
                    INNER JOIN instagram_shop shop ON shop.id = picture.shop_id
                    WHERE picture.deletedAt IS NULL
                        AND picture.isLocked = 0
                        AND picture.isShown = 1
                        AND picture.isTestimonial = 0
                        AND shop.deletedAt IS NULL
                        AND shop.isLocked = 0
                        AND shop.expirydate IS NOT NULL 
                        AND shop.expirydate > now()
                        AND picture.id NOT IN (SELECT style.picture_id FROM instagram_picture_style style)

this query for some reason is running slow. How can I rewrite this without having to use NOT IN

score 0 · Accepted Answer · edited May 23 '17 at 12:09

SELECT picture.id, picture.caption, picture.lowresimageurl, picture.medresimageurl
                    FROM instagram_shop_picture picture 
                    INNER JOIN instagram_shop shop ON shop.id = picture.shop_id
                    left join instagram_picture_style style on style.picture_id = picture.id
                    WHERE picture.deletedAt IS NULL 
                        AND picture.isLocked = 0
                        AND picture.isShown = 1
                        AND picture.isTestimonial = 0
                        AND shop.deletedAt IS NULL
                        AND shop.isLocked = 0
                        AND shop.expirydate IS NOT NULL 
                        AND shop.expirydate > now()
                        and style.picture_id is null

Change from the sub query to left join in for instagram_picture_style table and add a new condition style.picture_id is null

Join will be faster than subquery

rewriting query to avoid not in and boost performance

1 Answers1