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

fix_course_sortorder performance improvement

    XMLWordPrintable

    Details

      Description

      In function fix_course_sortorder(), this query is a bottleneck on big instances:

          // categories having courses with sortorder duplicates or having gaps in sortorder
          $sql = "SELECT DISTINCT c1.category AS id , cc.sortorder
                    FROM {course} c1
                    JOIN {course} c2 ON c1.sortorder = c2.sortorder
                    JOIN {course_categories} cc ON (c1.category = cc.id)
                   WHERE c1.id <> c2.id";
          $fixcategories = $DB->get_records_sql($sql);

      In one of our Moodles that has over 20k courses, and on my sandbox it takes over 30 seconds to execute. The fix for MySQL is to simply add index on course.sortorder, which brings the execution of the query down to split of a second.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              skodak Petr Skoda
              Reporter:
              tmuras Tomasz Muras
              Integrator:
              Dan Poltawski
              Tester:
              Michael de Raadt
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              3 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                25/Jun/12