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

Quiz report download: slow query in MySQL with large databases if when including users who have or have not attempted the quiz

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Minor Minor
    • None
    • 4.1.5
    • Performance, Quiz
    • MOODLE_401_STABLE

      We have encountered an issue in which downloading CSV on the Quiz Results Page is failing when selecting Enrolled users who have, or have not, attempted the quiz. The following SQL is taking 20minutes to execute:
      The quiz has 2 questions only with about 40+ attempts.

       

      SELECT
          qas.id,
          qa.id AS questionattemptid,
          qa.questionusageid,
          qa.slot,
          qa.behaviour,
          qa.questionid,
          qa.variant,
          qa.maxmark,
          qa.minfraction,
          qa.maxfraction,
          qa.flagged,
          qa.questionsummary,
          qa.rightanswer,
          qa.responsesummary,
          qa.timemodified,
          qas.id AS attemptstepid,
          qas.sequencenumber,
          qas.state,
          qas.fraction,
          qas.timecreated,
          qas.userid
       
      FROM (
                      SELECT DISTINCT quiza.uniqueid
                        FROM  mdl_user u
       JOIN mdl_user_info_field uf1f_1 ON LOWER(uf1f_1.shortname) COLLATE utf8mb4_bin = LOWER('section')
                             LEFT JOIN mdl_user_info_data uf1d_1 ON uf1d_1.fieldid = uf1f_1.id
                                       AND uf1d_1.userid = u.id JOIN mdl_user_info_field uf1f_2 ON LOWER(uf1f_2.shortname) COLLATE utf8mb4_bin = LOWER('subgroup')
                             LEFT JOIN mdl_user_info_data uf1d_2 ON uf1d_2.fieldid = uf1f_2.id
                                       AND uf1d_2.userid = u.id JOIN mdl_user_info_field uf1f_3 ON LOWER(uf1f_3.shortname) COLLATE utf8mb4_bin = LOWER('affiliation')
                             LEFT JOIN mdl_user_info_data uf1d_3 ON uf1d_3.fieldid = uf1f_3.id
                                       AND uf1d_3.userid = u.id JOIN mdl_user_info_field uf1f_4 ON LOWER(uf1f_4.shortname) COLLATE utf8mb4_bin = LOWER('department')
                             LEFT JOIN mdl_user_info_data uf1d_4 ON uf1d_4.fieldid = uf1f_4.id
                                       AND uf1d_4.userid = u.id 
      LEFT JOIN mdl_quiz_attempts quiza ON
                                          quiza.userid = u.id AND quiza.quiz = '202045'
      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 = '195153')
      JOIN (SELECT DISTINCT userid
                                      FROM mdl_role_assignments
                                     WHERE contextid IN (1,129008,129109,129150,3906617,4035964,4512544)
                                           AND roleid IN (5,12,21,22,6,17,16)
                                   ) ra ON ra.userid = u.id
                       WHERE (quiza.preview = 0 OR quiza.preview IS NULL) AND 1 = 1 AND u.deleted = 0 AND u.id <> '1' AND u.deleted = 0
                          ) quizasubquery
                      JOIN mdl_question_attempts qa ON qa.questionusageid = quizasubquery.uniqueid
      JOIN mdl_question_attempt_steps qas ON qas.questionattemptid = qa.id
              AND qas.sequencenumber = (
                      SELECT MAX(sequencenumber)
                      FROM mdl_question_attempt_steps
                      WHERE questionattemptid = qa.id
                  )
       
      WHERE
          1 = 1
           AND qa.slot IN ('1','2') 

      The above slowness seems to be caused by the last clause "AND qa.slot IN ('1','2'). It appears that it is performing a full table scan at table mdl_question_attempts, which in our case has about 65M records.

      Removing/commenting that line executes the query under 1 second. 

       

      I have attempted to get the code  which generates the SQL above and it looks to be the code at question/engine/datalib.php function load_questions_usages_latest_steps.

        1. explain.png
          60 kB
          Joshua Ebarvia

            Unassigned Unassigned
            jebarvia Joshua Ebarvia
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.