Need help with optimizing "not in" query

Question

I have an SQL query that I am looking to optimize.

SELECT * 
FROM QUEUE_SMS_ALERT Q1
where ALERT_ORIGIN = "FOO" 
AND RECORD_ID is null 
and PHONE NOT IN (
    SELECT DISTINCT PHONE 
    FROM QUEUE_SMS_ALERT Q2 
    where Q2.ALERT_ORIGIN = "BAR"
);

Basically need to get all rows where ALERT_ORIGIN is "FOO" Which do not have a corresponding row in the same table with ALERT_ORIGIN "BAR". The table contains abt 17000 rows and there are only abt 1000 records with ALERT_ORIGIN "BAR". So my query is supposed to give me abt 16000 rows.

EDIT : The current query is very slow. I do not have any indexes currently.

Do you have indexes, keys etc? Is it slow now? Is PHONE nullable? — gbn
– gbn, Commented Aug 26, 2011 at 8:02
Consider putting indexes on columns ALERT_ORIGIN and PHONE — Bohemian
– Bohemian ♦, Commented Aug 26, 2011 at 8:03
And you can remove the DISTINCT from the ...PHONE NOT IN (SELECT DISTINCT PHONE ..., it's not needed. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 26, 2011 at 8:06
@Shreyas, which version of SQL (Oracle, SQLServer, MySQL, etc.) are you using? — user359040
– user359040, Commented Aug 26, 2011 at 8:45

gbn · Accepted Answer · 2011-08-26 08:05:49Z

3

I'm guessing that you have NULL values in the phone column which means NOT IN doesn't work (so it's "fix" not "optimise"). So I've written it with NOT EXISTS:

SELECT *
FROM QUEUE_SMS_ALERT Q1 
WHERE
    Q1.ALERT_ORIGIN = 'FOO'
    AND
    Q1.RECORD_ID is null
    AND 
    NOT EXISTS (SELECT *
        FROM QUEUE_SMS_ALERT Q2
        WHERE 
            Q2.ALERT_ORIGIN = 'BAR'
            AND
            Q1.PHONE = Q2.PHONE)

If it is slow rather than "wrong" then you need to use indexes. What do you have now? For this query, you need an index on (ALERT_ORIGIN, PHONE, RECORD_ID).

Note: use single quotes for string delimiters

answered Aug 26, 2011 at 8:05

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

a'r Over a year ago

Why did you include RECORD_ID in the index suggestion? This column can be NULL, which might make the index less useful for this query.

gbn Over a year ago

@a'r: It's in the WHERE clause. NULLs are indexed too.

a'r Over a year ago

@gbn, it depends on the database. NULLs are not always indexed in Oracle for example.

gbn Over a year ago

@a'r: In SQL Server an index entry has the NULL bitmap. Anyway, OP is happy :-)

Collectives™ on Stack Overflow

Need help with optimizing "not in" query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related