Why is this query so slow?

Question

I have a quiz site with approx 2000 questions. The function in question worked great when I wasn't tracking that many users, but now there are 300,000 question that users have seen. The function only checks if a user has seen a question and returns questions that they have not seen.

SELECT q.id
FROM questions q 
WHERE 
      q.chapter_id = ? 
      && (q.module = 1 || q.module = ?)
      && q.question_type != 4 
      && q.id NOT IN(
          SELECT u.question_id 
          FROM user_quiz_questions u
          WHERE user_id = ? && chapter_id = ?
      ) 
ORDER BY RAND() 
LIMIT ?

Just to elaborate on the code a little:

• questions is the table where all the questions are.
• user_quiz_questions is the table that tracks the users previous answers.
• id is the id for the question
• module checks if it in the users study group
• question_type makes sure it is an active question.

Any help on this issue would be great. Thanks.

Answer 1

A few places I would expect to start. Please look at query plans but my guess is that this is a pretty simple case here of antijoins performing poorly (yes, I have been bitten by something similar even on PostgreSQL so I don't think this is unique to MySQL though InnoDB has some design decisions which make me think this could be particularly bad there).

In general, one of the basic problems with NOT IN is that most db's I have seen end up doing this as a nested loop check using one means or another.

The first thing you should do is rewrite this as an outer join (for example a left join):

SELECT q.id
FROM questions q 
LEFT JOIN user_quiz_questions u 
          ON (u.question_id = q.id 
              && user_id = ? && chapter_id = ?)
WHERE 
      q.chapter_id = ? 
      && (q.module = 1 || q.module = ?)
      && q.question_type != 4 
      && u.question_id IS NULL
ORDER BY RAND() 
LIMIT ?

The planner has a lot more freedom for how to address this and left joins are far more mature in MySQL than are subselects making me suspect that this will perform much better.

Edit: As has been pointed out to me, this may be a misreading of your question. Are you saying you have 2000 questions or 300000 seen questions plus 2000 questions that haven't been seen yet? These are big differences and you are likely to need an index on join fields anyway if you don't have one.