Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-61605

Improve quiz report performance by adding index on questionusageid in quiz_overview_regrades

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.2.5, 3.2.6, 3.3.2, 3.3.3, 3.4, 3.4.1
    • Fix Version/s: 3.5
    • Component/s: Performance, Quiz
    • Labels:
    • Testing Instructions:
      Hide

      The main thing is to verify that there are no regressions:

      1. Upgrade your Moodle, verify there are no errors.
      2. Create (or find an existing) quiz with some student attempts.
      3. Make a safe edit to one of the questions (e.g. change the right answer to a short-answer question, or change which multiple-choice or true-false answer is graded right).
      4. Go to Quiz admin -> results -> grades, and regrade the attempts.
      5. Verify that works without errors, and then the report shows which grades changed.
      Show
      The main thing is to verify that there are no regressions: Upgrade your Moodle, verify there are no errors. Create (or find an existing) quiz with some student attempts. Make a safe edit to one of the questions (e.g. change the right answer to a short-answer question, or change which multiple-choice or true-false answer is graded right). Go to Quiz admin -> results -> grades, and regrade the attempts. Verify that works without errors, and then the report shows which grades changed.
    • Affected Branches:
      MOODLE_32_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE
    • Fixed Branches:
      MOODLE_35_STABLE
    • Pull Master Branch:

      Description

      We're noticing several slow queries from quiz_overview_regrades joins on questionusageid in mod/quiz/report/overview/overview_table.php and mod/quiz/report/overview/report.php.

      Currently we have about 30,000 entries in quiz_overview_regrades, causing long queries like this:

      # Query_time: 5.461517  Lock_time: 0.000181  Rows_sent: 0  Rows_examined: 30645  Rows_affected: 0
      # Bytes_sent: 50
      SET timestamp=1520346177;
      SELECT 1
                        FROM  mdl_user u
      LEFT JOIN mdl_quiz_attempts quiza ON
                                          quiza.userid = u.id AND quiza.quiz = '109764'
      JOIN mdl_user_enrolments ej1_ue ON ej1_ue.userid = u.id
      JOIN mdl_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid AND ej1_e.courseid = '12345')
                        JOIN mdl_quiz_overview_regrades qor ON qor.questionusageid = quiza.uniqueid
                       WHERE quiza.preview = 0 AND quiza.id IS NOT NULL AND 1 = 1 AND u.deleted = 0 LIMIT 0, 1;
      

      and more importantly

      # Query_time: 10.740157  Lock_time: 0.000270  Rows_sent: 110  Rows_examined: 47391836  Rows_affected: 0
      # Bytes_sent: 18691
      SET timestamp=1520346187;
      SELECT
                      DISTINCT CONCAT(u.id, '#', COALESCE(quiza.attempt, 0)) AS uniqueid,
      (CASE WHEN (quiza.state = 'finished' AND NOT EXISTS (
                                 SELECT 1 FROM mdl_quiz_attempts qa2
                                  WHERE qa2.quiz = quiza.quiz AND
                                      qa2.userid = quiza.userid AND
                                       qa2.state = 'finished' AND (
                      COALESCE(qa2.sumgrades, 0) > COALESCE(quiza.sumgrades, 0) OR
                     (COALESCE(qa2.sumgrades, 0) = COALESCE(quiza.sumgrades, 0) AND qa2.attempt < quiza.attempt)
                                      ))) THEN 1 ELSE 0 END) AS gradedattempt,
                      quiza.uniqueid AS usageid,
                      quiza.id AS attempt,
                      u.id AS userid,
                      u.idnumber, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname,
                      u.picture,
                      u.imagealt,
                      u.institution,
                      u.department,
                      u.email,
                      quiza.state,
                      quiza.sumgrades,
                      quiza.timefinish,
                      quiza.timestart,
                      CASE WHEN quiza.timefinish = 0 THEN null
                           WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart
                           ELSE 0 END AS duration, COALESCE((
                                      SELECT MAX(qqr.regraded)
                                        FROM mdl_quiz_overview_regrades qqr
                                       WHERE qqr.questionusageid = quiza.uniqueid
                                ), -1) AS regraded
                      FROM  mdl_user u
      LEFT JOIN mdl_quiz_attempts quiza ON
                                          quiza.userid = u.id AND quiza.quiz = '109764'
      JOIN mdl_user_enrolments ej1_ue ON ej1_ue.userid = u.id
      JOIN mdl_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid AND ej1_e.courseid = '12345')
                      WHERE quiza.preview = 0 AND quiza.id IS NOT NULL AND 1 = 1 AND u.deleted = 0
                      ORDER BY quiza.id ASC LIMIT 0, 110;
      

      Adding an index on questionusageid fixes the problem. Here are the EXPLAIN statements before and after executing

      ALTER TABLE mdl_quiz_overview_regrades ADD KEY  mdl_quizoverregr_que_ix (questionusageid);
      

      BEFORE:

      id select_type table partitions type possible_keys key key_len ref rows filtered Extra
      1 SIMPLE ej1_e NULL ref PRIMARY,mdl_enro_cou_ix mdl_enro_cou_ix 8 const 1 100.00 Using where; Using index
      1 SIMPLE ej1_ue NULL ref mdl_userenro_enruse_uix,mdl_userenro_enr_ix,mdl_userenro_use_ix mdl_userenro_enruse_uix 8 moodle.ej1_e.id 24 100.00 Using index
      1 SIMPLE u NULL eq_ref PRIMARY,mdl_user_del_ix PRIMARY 8 moodle.ej1_ue.userid 1 50.00 Using where
      1 SIMPLE quiza NULL ref PRIMARY,mdl_quizatte_uni_uix,mdl_quizatte_quiuseatt_uix,mdl_quizatte_qui_ix,mdl_quizatte_use_ix mdl_quizatte_quiuseatt_uix 16 const,moodle.ej1_ue.userid 1 9.00 Using index condition; Using where
      1 SIMPLE qor NULL ALL NULL NULL NULL NULL 26907 10.00 Using where; Using join buffer (Block Nested Loop)

      AFTER:

      id select_type table partitions type possible_keys key key_len ref rows filtered Extra
      1 SIMPLE ej1_e NULL ref PRIMARY,mdl_enro_cou_ix mdl_enro_cou_ix 8 const 1 100.00 Using where; Using index
      1 SIMPLE ej1_ue NULL ref mdl_userenro_enruse_uix,mdl_userenro_enr_ix,mdl_userenro_use_ix mdl_userenro_enruse_uix 8 moodle.ej1_e.id 24 100.00 Using index
      1 SIMPLE u NULL eq_ref PRIMARY,mdl_user_del_ix PRIMARY 8 moodle.ej1_ue.userid 1 50.00 Using where
      1 SIMPLE quiza NULL ref PRIMARY,mdl_quizatte_uni_uix,mdl_quizatte_quiuseatt_uix,mdl_quizatte_qui_ix,mdl_quizatte_use_ix mdl_quizatte_quiuseatt_uix 16 const,moodle.ej1_ue.userid 2 9.00 Using index condition; Using where
      1 SIMPLE qor NULL ref mdl_quizoverregr_que_ix mdl_quizoverregr_que_ix 8 moodle.quiza.uniqueid 1 100.00 Using index

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  17/May/18