Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
3  
An execution plan alongside with table and index definitions would definitely help. –  dezso Feb 21 '13 at 8:38
    
An index on user_quiz_questions (user_id, chapter_id, question_id) will be helpful. –  ypercube Feb 21 '13 at 12:48
    
Indexes... so that was a oversight on my part, as I was trying to not fill my database with too many indexes, I failed to put some basic ones in on a few tables. That really sped it up. It went from a minute(actually) to a second (approx). Thanks. –  ChristopherMarcel Feb 21 '13 at 22:03

1 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.

share|improve this answer
    
To clarify, I have 2000 questions in one table. I am keeping another table in the db (the one with 300,00) to keep track of the questions users have seen; that table also tracks whether they got it right or wrong. –  ChristopherMarcel Feb 21 '13 at 21:59
    
I added indexes as stated above and it was a huge improvement. I really appreciate the information you provided and it gave me some things to look at for self improvement. Thanks. –  ChristopherMarcel Feb 21 '13 at 22:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.