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

Question

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

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

Answer 1

嵌套查询始终是性能瓶颈。尝试改用 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 ))