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

Very inefficient query in grade provider (MySQL)

    XMLWordPrintable

    Details

      Description

      The privacy provider for core_grade calls the following SQL in \core_grades\privacy\provider::get_contexts_for_userid(). On MySQL at least, this is incredibly inefficient as it causes full table scans on grade_grades and grade_grades_history. (We have seen it take over 24 hours to run, with 800,000+ and 15.5 million records in those tables respectively.)

      SELECT DISTINCT ctx.id
       FROM {grade_items} gi
       JOIN {context} ctx
       ON ctx.instanceid = gi.courseid
       AND ctx.contextlevel = :courselevel
       LEFT JOIN {grade_grades} gg
       ON gg.itemid = gi.id
       AND (gg.userid = :userid1 OR gg.usermodified = :userid2)
       LEFT JOIN {grade_grades_history} ggh
       ON ggh.itemid = gi.id
       AND (
       ggh.userid = :userid3
       OR ggh.loggeduser = :userid4
       OR ggh.usermodified = :userid5
       )
       WHERE gg.id IS NOT NULL
       OR ggh.id IS NOT NULL
      
      

      This seems to be down to MySQL being unable to optimise the query to use indexes for all the joins and the filters.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                maherne Michael Aherne
                Reporter:
                maherne Michael Aherne
                Peer reviewer:
                Andrew Nicols
                Integrator:
                Jake Dallimore
                Tester:
                CiBoT
                Participants:
                Component watchers:
                Jake Dallimore, Jun Pataleta, Ryan Wyllie, Andrew Nicols, Mathew May, Michael Hawkins, Shamim Rezaie, Simey Lameze
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  14/Jan/19