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

SCORM - Slow query in report graph

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Deferred
    • Affects Version/s: 2.6
    • Fix Version/s: None
    • Component/s: SCORM
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_26_STABLE

      Description

      There is a query that can become exceptionally slow on large datasets in Moodle 2.6.1+ /mod/scorm/report/graphs/graph.php (line 74-78)

      We are seeing it take up to 38 seconds to return from the query with just under 1 million rows in `mdl_scorm_scoes_track`.
      This is on a Solaris MySQL 5.5 server (can replicate on RHEL - MySQL 5.1).

      This seems to be because the query does a DISTINCT on uniqueid which is non-indexable.

      I seem to get the same result set by removing the DISTINCT and doing a GROUP BY on u.id and st.attempt instead. Though I did need to add an index to '`mdl_scorm_scoes_track`.attempt' first. This reduced query time to approx 0.021 seconds. Not sure what effect that has on graph though.

      Current SQL:

      SELECT DISTINCT CONCAT(u.id, '#', COALESCE(st.attempt, 0)) AS uniqueid, st.scormid AS scormid, st.attempt AS attempt, u.id AS userid,u.picture,u.firstname,u.lastname,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,u.idnumber
      FROM mdl_user u
      LEFT JOIN mdl_scorm_scoes_track st
      ON st.userid = u.id AND st.scormid = ?
      WHERE u.id IN (?) AND (st.userid IS NOT NULL OR st.userid IS NULL) 
      ORDER BY uniqueid
       LIMIT 0, 400
      

      Proposed SQL:

      SELECT CONCAT(u.id, '#', COALESCE(st.attempt, 0)) AS uniqueid, st.scormid AS scormid, st.attempt AS attempt, u.id AS userid,u.picture,u.firstname,u.lastname,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,u.idnumber
      FROM mdl_user u
      LEFT JOIN mdl_scorm_scoes_track st
      ON st.userid = u.id AND st.scormid = ?
      WHERE u.id IN (?) AND (st.userid IS NOT NULL OR st.userid IS NULL) 
      GROUP BY u.id,st.attempt
      ORDER BY uniqueid
       LIMIT 0, 400
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                danmarsden Dan Marsden
                Reporter:
                skylarkelty Skylar Kelty
                Participants:
                Component watchers:
                Damyon Wiese, Dan Marsden, Matteo Scaramuccia, Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias
              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: