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

Extremely slow query in calendar for large sites

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Set the calendar_adminseesall config setting to true
      Visit a calendar page
      Ensure that all entries are correctly shown

      Run the old query and the new query to ensure that the same results are always returned. For convenience, I've pasted them below:

      Old code:

      SELECT DISTINCT
      		 
          c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
      		 
      FROM mdl_course c
      		 
      JOIN mdl_event e ON e.courseid = c.id
      		 
      LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
      		 
      ;

      New code:

      SELECT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
      		 
      FROM mdl_course c
      		 
      LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
      		 
      WHERE EXISTS (SELECT 1 FROM mdl_event e WHERE e.courseid = c.id)
      		 
      ;

      Show
      Set the calendar_adminseesall config setting to true Visit a calendar page Ensure that all entries are correctly shown Run the old query and the new query to ensure that the same results are always returned. For convenience, I've pasted them below: Old code: SELECT DISTINCT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance FROM mdl_course c JOIN mdl_event e ON e.courseid = c.id LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50) ; New code: SELECT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance FROM mdl_course c LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50) WHERE EXISTS (SELECT 1 FROM mdl_event e WHERE e.courseid = c.id) ;
    • Difficulty:
      Moderate
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-32340-master-2
    • Pull Master Diff URL:

      Description

      It seems MDL-31086 introduces a very slow query in calendar, for large sites.

      $sql = "SELECT DISTINCT c.* $select
      		 
      FROM {course} c
      		 
      JOIN {event} e ON e.courseid = c.id
      		 
      $join";

      In my case, after upgrading to 2.1.5 via CLI, I could not login to the site as an admin due to hitting 5 minute connection timeout. Checking postgres showed this query running for several minutes - it appears to try and sort by all columns in the query, then return the top 20 for the get_records_sql limit. (So, not a very good plan, but...)

      Since we know course id is unique enough, it is much nicer on the database to advise it only to make the results unique by c.id i.e:

      -        $sql = "SELECT DISTINCT c.* $select
      		 
      +        $sql = "SELECT DISTINCT ON (c.id) c.* $select

      This makes the query return in only 10ms for this site. I'm not sure if this is compatible enough syntax for core, however.

      Github incoming.

        Attachments

          Issue Links

          is a regression caused by

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-31086 invalid sql in calendar/lib.php

          • Minor - Minor loss of function where workaround is possible
          • Closed

            Activity

              People

              Assignee:
              dobedobedoh Andrew Nicols
              Reporter:
              tlevi Tony Levi
              Peer reviewer:
              Dan Poltawski
              Integrator:
              Sam Hemelryk
              Tester:
              Michael de Raadt
              Participants:
              Component watchers:
              Andrew Nicols, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Matteo Scaramuccia, Andrew Nicols, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                9/Jul/12