sql - 在另一个表的嵌套SELECT上执行NOT IN时，查询速度很慢

SELECT problems . * , users.id AS new_user_id, users.nick AS nick
FROM problems, users
WHERE problems.deleted =0
AND problems.topic_id =1
AND problems.user_id = users.id
AND problems.id NOT
IN (
  SELECT DISTINCT (problem_id)
  FROM problems_attempted
  WHERE user_id =1
  AND total_questions = ( attempted_right + attempted_wrong + skipped )
)
ORDER BY problems.updated DESC

能否优化此查询以获得更好的性能？

最佳答案

嵌套查询始终是性能瓶颈。尝试改用join

select p.*, u.*
from problems p join users u
on p.user_id = u.id
join problems_attempted pa on pa.problem_id = p.id
where not (pa.user_id = 1
and total_questions = ( attempted_right + attempted_wrong + skipped ))