Query is slow while doing a NOT IN on a nested SELECT from another table

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

Can this query be optimized for a better performance?

Answer 1

Nested queries are always a performance bottleneck. Try to use join instead

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 ))

Answer 2

try this:

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

    NOT EXISTS (
        SELECT *
        FROM problems_attempted
        WHERE 
            problem_id = problems.id 
            AND user_id = 1
            AND total_questions = ( attempted_right + attempted_wrong + skipped )
         )