MySQL IN 条件子查询

Question

我有一个问题和答案列表，以及一个根据正确答案的百分比过滤问题的选项。因此，我对列表使用以下查询:

SELECT 
question_id, 
text
FROM 
test_answers LEFT JOIN test_questions ON test_questions.id = test_answers.question_id 
LEFT JOIN test_categories ON test_questions.`category_id` = test_categories.id 
WHERE `question_id` IN(question IDS) 
GROUP BY `question_id` 
ORDER BY `question_id` DESC;

并使用另一个查询来查找问题 IDS，其正确答案的百分比在给定范围内。查询如下:

SELECT q1.question_id FROM (
        SELECT test_answers.question_id AS question_id, 
        SUM( IF( test_answers.correct_answer =1, 1, 0 ) ) AS correct_answers, 
        SUM( IF( test_answers.correct_answer !=1, 1, 0 ) ) AS incorrect_answers, 
        round( ( SUM( IF( test_answers.correct_answer =1, 1, 0 ) ) / ( SUM( IF( test_answers.correct_answer =1, 1, 0 ) ) + SUM( IF( test_answers.correct_answer !=1, 1, 0 ) ) ) *100 ) , 2 ) AS percentage 
        FROM test_replies 
        JOIN test_answers ON test_replies.answer_id = test_answers.id 
        GROUP BY test_answers.question_id 
        HAVING percentage between 80 and 89 AND correct_answers >25
) AS q1

现在的问题是，第二个查询返回了近 4000 个问题 ID，并且在不久的将来还会增加，可能会达到 10k 或更多。所以我真的很想优化查询，因为它会极大地影响性能。任何人都可以建议更好的方法吗？

Answer 1

尝试加入而不是IN，看看是否有帮助。 (sql未测试)

SELECT 
    ta.question_id, text
FROM 
    (
        SELECT test_answers.question_id AS question_id, 
        SUM( IF( test_answers.correct_answer =1, 1, 0 ) ) AS correct_answers, 
        SUM( IF( test_answers.correct_answer !=1, 1, 0 ) ) AS incorrect_answers, 
        round( ( SUM( IF( test_answers.correct_answer =1, 1, 0 ) ) / ( SUM( IF( test_answers.correct_answer =1, 1, 0 ) ) + SUM( IF( test_answers.correct_answer !=1, 1, 0 ) ) ) *100 ) , 2 ) AS percentage 
        FROM test_replies 
        JOIN test_answers ON test_replies.answer_id = test_answers.id 
        GROUP BY test_answers.question_id 
        HAVING percentage between 80 and 89 AND correct_answers >25
    ) AS q1
INNER JOIN 
    test_answers ta USING (question_id) 
LEFT JOIN 
    test_questions ON test_questions.id = ta.question_id 
LEFT JOIN 
    test_categories ON test_questions.`category_id` = test_categories.id 
GROUP BY 
    ta.question_id`
ORDER BY 
    ta.question_id DESC;