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

Inefficient query during Moodle upgrade on course_section table.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.6.3, 2.7
    • Fix Version/s: 2.6.6, 2.7.3
    • Component/s: Installation
    • Environment:
      CentOS 6, MySQL 5.6, PHP 5.3.3
    • Database:
      MySQL
    • Testing Instructions:
      Hide
      • Install a version of Moodle below 2012042300.00
      • Ensure it has sevral courses and plenty of course sections.
      • Ensure some activities in that course have availability conditions
      • Upgrade Moodle

      With the patch the 2012042300.00 should be much faster, especially for a site with large numbers of course sections.

      Show
      Install a version of Moodle below 2012042300.00 Ensure it has sevral courses and plenty of course sections. Ensure some activities in that course have availability conditions Upgrade Moodle With the patch the 2012042300.00 should be much faster, especially for a site with large numbers of course sections.
    • Affected Branches:
      MOODLE_26_STABLE, MOODLE_27_STABLE
    • Fixed Branches:
      MOODLE_26_STABLE, MOODLE_27_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      In the Moodle upgrade script for the step 2012042300.00 the database index for course, section is changed to be unique. There is also a query in there to find any records that are not unique in there and fix them.

      Unfortunately this query is done after the index is removed, which makes it horribly inefficient, especially on large datasets.

      The query

      SELECT DISTINCT cs.id, cs.course
      FROM

      {course_sections} cs
      INNER JOIN {course_sections}

      older
      ON cs.course = older.course AND cs.section = older.section
      AND older.id < cs.id;

      Example execution plans

      On our database:

      1. id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
        '1', 'SIMPLE', 'cs', 'ALL', 'PRIMARY', NULL, NULL, NULL, '203273', 'Using temporary'
        '1', 'SIMPLE', 'older', 'ALL', 'PRIMARY', NULL, NULL, NULL, '203273', 'Range checked for each record (index map: 0x1); Distinct'

      Compared to a plan when the index is present:

      1. id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
        '1', 'SIMPLE', 'cs', 'index', 'PRIMARY,mdl_coursect_cousec_ix', 'mdl_coursect_cousec_ix', '16', NULL, '382452', 'Using index; Using temporary'
        '1', 'SIMPLE', 'older', 'ref', 'PRIMARY,mdl_coursect_cousec_ix', 'mdl_coursect_cousec_ix', '16', 'moo.cs.course,moo.cs.section', '1', 'Using where; Using index; Distinct'

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nmagill Neill Magill
              Reporter:
              nmagill Neill Magill
              Peer reviewer:
              Michael Aherne
              Integrator:
              Sam Hemelryk
              Tester:
              John Okely
              Participants:
              Component watchers:
              Matteo Scaramuccia, 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:
                10/Nov/14