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:
questionsis the table where all the questions are.
user_quiz_questionsis the table that tracks the users previous answers.
idis the id for the question
modulechecks if it in the users study group
question_typemakes sure it is an active question.
Any help on this issue would be great. Thanks.