How can I optimize this mysql query? I'm using the IN() operator, but I believe it's not the way to do it.
如何优化这个mysql查询?我正在使用IN()运算符，但我认为它不是这样做的。
select *
from users
where usid NOT in
(
select usid
from images
where status=0
)
and us_status=0
3 个解决方案
#1
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.
这避免了使用性能差的产品。
#2
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
Must read the article : http://www.w3schools.com/sql/sql_join.asp
必须阅读文章:http://www.w3schools.com/sql/sql_join.asp
#3
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
#1
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.
这避免了使用性能差的产品。
#2
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
Must read the article : http://www.w3schools.com/sql/sql_join.asp
必须阅读文章:http://www.w3schools.com/sql/sql_join.asp
#3
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