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](http://stackoverflow.com/q/2246772/1048425) and more importantly [this link](http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/) in the accepted answer should help you out here. In summary, for MySQL use `LEFT JOIN/IS NULL` to remove matching records. — GarethD, Sep 11 '15 at 09:04

score 4 · Accepted Answer · answered Sep 11 '15 at 09:03

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 '15 at 09:03

Kickstart

Thanks very much, just instead us_status must be users.status yes? – Fəqan Çələbizadə Sep 11 '15 at 11:55
Your original query had us_status in it. If that was a typo for user.status then change it to that – Kickstart Sep 11 '15 at 12:22

Cosmin · Answer 2 · 2015-09-11T08:46:02.403

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

edited Sep 11 '15 at 08:46

answered Sep 11 '15 at 08:32

Cosmin

score 0 · Answer 3 · answered Sep 11 '15 at 08:35

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 '15 at 08:35

Happy Coding

3 Answers3