How can i optimize this mysql query ? i use IN() operator

Question

How can I optimize this mysql query? I'm using the IN() operator, but I believe it's not the way to do it.

select * 
from users 
where usid NOT in 
(
select usid 
from images 
where status=0
) 
and us_status=0

This question and more importantly this link in the accepted answer should help you out here. In summary, for MySQL use LEFT JOIN/IS NULL to remove matching records. — GarethD
– GarethD, Commented Sep 11, 2015 at 9:04

Kickstart · Accepted Answer · 2015-09-11 09:03:26Z

4

Using a LEFT OUTER JOIN

SELECT users.* 
FROM users 
LEFT OUTER JOIN images ON user.usid = images.usid AND images.status = 0
WHERE images.usid IS NULL
AND us_status = 0

This avoids using IN which can perform poorly.

answered Sep 11, 2015 at 9:03

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

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

2 Comments

Thanks very much, just instead us_status must be users.status yes?

Your original query had us_status in it. If that was a typo for user.status then change it to that

Happy Coding · Accepted Answer · 2015-09-11 08:35:23Z

0

You can use the following query :

SELECT us.* 
FROM users  as us
INNER JOIN images as img ON us.usid = img.usid AND img.status=1
WHERE us.us_status = 0

answered Sep 11, 2015 at 8:35

Happy Coding

2,5151 gold badge15 silver badges24 bronze badges

Cosmin · Accepted Answer · 2015-09-11 08:46:02Z

0

SELECT users.* 
FROM users 
LEFT JOIN images ON users.usid = images.usid AND images.status=1 AND images.usid IS NOT NULL
WHERE users.us_status = 0

answered Sep 11, 2015 at 8:32

Cosmin

1,49012 silver badges26 bronze badges