`카테고리별 평균 맞힌개수 챕터 전체조회` 쿼리 튜닝 필요

[kimdev0206]

해당 쿼리는 선택기록 쓰기 작업 이후, 실시간 데이터 제공을 위해 매우 빈번하게 호출된다.

EXPLAIN SELECT
  qc.category_seq AS categorySeq,
  qc.chapter_num AS chapterNum,
  (CASE               
    WHEN us.hit_question_count IS NULL
    THEN 0          
    ELSE ROUND(AVG(us.hit_question_count), 2)
  END) AS avgHitQuestionCount,
  qc.question_count AS questionCount      
FROM quiz_chapter AS qc
LEFT JOIN quiz_chapter_user_state AS us
  ON qc.chapter_seq = us.chapter_seq
GROUP BY
  qc.category_seq,
  qc.chapter_num;

다만, 실행계획을 통해 DBMS 자원을 많이 쓰는 쿼리임을 확인하였다.

id|select_type|table|type|possible_keys|key    |key_len|ref                     |rows|Extra                          |
--+-----------+-----+----+-------------+-------+-------+------------------------+----+-------------------------------+
 1|SIMPLE     |qc   |ALL |             |       |       |                        |26  |Using temporary; Using filesort|
 1|SIMPLE     |us   |ref |PRIMARY      |PRIMARY|4      |withbible.qc.chapter_seq|2   |                               |