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

Extremely slow query in calendar for large sites

          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.

              Gliffy Diagrams

                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

                      • Votes:
                        0 Vote for this issue
                        Watchers:
                        5 Start watching this issue

                        Dates

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