CODEWITHSUNDEEP
mysqlsqldatabase
M.M
M.M

Reputation: 1373

Optimizing sql double "Not in" clauses

I have the following query:

select * from table_2 
where 
user_1 not in (select user_id from users) or 
user_2 not in (select user_id from users)

The problem is that it takes very long time to execute. The tables have few millions of records. Is there any optimization that I can use for the query?

Upvotes: 1

Views: 1713

Answers (4)

Tufan Barış Yıldırım
Tufan Barış Yıldırım

Reputation: 3972

SELECT * FROM table_2 t2 
WHERE
NOT  EXISTS( SELECT 1 FROM users u WHERE  u.user_id IN(t2.user_1,t2.user_2) )

this should perform better.

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

This query will return all rows where user_1 and/or user_2 are not in the users table

select * from table_2 t1
where (select count(*) from users u where user_id in(t1.user_1,t1.user_2)) < 2

Upvotes: 0

Darius
Darius

Reputation: 53

You should try this:

select * from table_2
LEFT JOIN `users` AS u ON u.user_id = table_2.user_1
LEFT JOIN `users` AS u2 ON u2.user_id = table_2.user_2
WHERE u.user_id is NULL and u2.user_id is NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

First, rewrite these as not exists . . . this often has better performance:

select t2.*
from table_2 t2
where not exists (select 1 from users u where u.user_id = t2.user_1) or 
      not exists (select 1 from users u where u.user_id = t2.user_2);

More importantly, create an index on users(user_id), if one does not already exist.

Upvotes: 3

Related Questions