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

Database query for Course Participation report can crash large sites

    XMLWordPrintable

    Details

      Description

      We have had a few large clients report severe performance degradation, including failures to load pages. The problem was traced to a database query in the Course Participation report. Example query to follow. On large sites with role overrides, this query can topple a database server.

      SELECT ra.userid, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname, u.idnumber, l.actioncount AS count
                        FROM (SELECT DISTINCT userid FROM mdl_role_assignments WHERE contextid IN ('1065965','20926','1') AND roleid = '2' ) ra
                        JOIN mdl_user u ON u.id = ra.userid
                   LEFT JOIN (
                          SELECT userid, COUNT(crud) AS actioncount
                            FROM mdl_logstore_standard_log
                           WHERE contextinstanceid = '861505'
                             AND timecreated > '1420869600' AND crud IN ('c','r','u','d')
                             AND edulevel = '2'
                             AND anonymous = 0
                             AND contextlevel = '70'
                             AND (origin = 'web' OR origin = 'ws')
                        GROUP BY userid) l ON (l.userid = ra.userid) ORDER BY lastname DESC LIMIT 0, 20
      

        Attachments

          Activity

            People

            Assignee:
            marina Marina Glancy
            Reporter:
            cfollin Chris Follin
            Peer reviewer:
            Rajesh Taneja
            Integrator:
            Dan Poltawski
            Tester:
            Mark Nelson
            Participants:
            Component watchers:
            Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              14/Sep/15