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:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Andrew Nicols, Jun Pataleta, 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