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

mod_quiz statistics SQL query inefficient on huge MySQL DB

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.5
    • Fix Version/s: STABLE backlog
    • Component/s: Quiz
    • Labels:
    • Affected Branches:
      MOODLE_25_STABLE

      Description

      Moodle Version: '2.5.1+ (Build: 20130815)'
      DB: MySQL 5.1.69

      In line 108 of the file mod/quiz/report/statistics/qstats.php
      the following query is thrown at our MySQL DB which has
      56Mio (Million) rows in the table

      {question_attempt_steps}.
      The query performs very badly (to say the least)...

      $this->lateststeps = $DB->get_records_sql("
      SELECT
      qas.id,
      quiza.sumgrades,
      qa.questionid,
      qa.slot,
      qa.maxmark,
      qas.fraction * qa.maxmark as mark

      FROM $fromqa
      JOIN {question_attempts} qa ON qa.questionusageid = quiza.uniqueid
      JOIN (
      SELECT questionattemptid, MAX(id) AS latestid
      FROM {question_attempt_steps}

      GROUP BY questionattemptid
      ) lateststepid ON lateststepid.questionattemptid = qa.id
      JOIN

      {question_attempt_steps} qas ON qas.id = lateststepid.latestid

      WHERE
      qa.slot $qsql AND
      $whereqa", $qparams + $qaparams);
      }

      This is a killer! Even the subquery in the second join alone virtually never terminates:
      SELECT questionattemptid, MAX(id) AS latestid
      FROM {question_attempt_steps}

      GROUP BY questionattemptid

      I suppose this has to go through all the rows...
      On top of that MySQL has the subquery-optimisation-problem, but I'm not sure whether this is the cause here.

      I originally copied this code for the statistics of my offlinequiz module and did the following change which helped significantly. At least, the subquery is constrained (just replace offlinequiz by quiz in your mind):

      // Trying to make this work on MySQL
      // First we get the questionattemptids that we actually need.
      $questionattemptids = $DB->get_fieldset_sql("
      SELECT qa.id
      FROM $fromqa
      JOIN

      {question_attempts} qa ON qa.questionusageid = offlinequiza.usageid
      WHERE qa.questionid $qsql
      AND $whereqa", $qparams + $qaparams);

      list($qaidssql, $qaidsparams) = $DB->get_in_or_equal($questionattemptids, SQL_PARAMS_NAMED, 'qaid');

      $params = array_merge($qparams, $qaparams, $qaidsparams);

      // works already quite a bit faster
      $this->lateststeps = $DB->get_records_sql("
      SELECT
      qas.id,
      offlinequiza.sumgrades,
      qa.questionid,
      qa.slot,
      qa.maxmark,
      qas.fraction * qa.maxmark as mark

      FROM $fromqa
      JOIN {question_attempts}

      qa ON qa.questionusageid = offlinequiza.usageid
      JOIN (
      SELECT questionattemptid, MAX(id) AS latestid
      FROM

      {question_attempt_steps} qass
      WHERE qass.questionattemptid $qaidssql
      GROUP BY questionattemptid
      ) lateststepid ON lateststepid.questionattemptid = qa.id
      JOIN {question_attempt_steps}

      qas ON qas.id = lateststepid.latestid

      WHERE
      qa.questionid $qsql AND
      $whereqa", $params);

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              jzimmer Juergen Zimmer
              Participants:
              Component watchers:
              Tim Hunt, Andrew Nicols, Mathew May, Michael Hawkins, Shamim Rezaie, Simey Lameze
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: