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

New index for role_assignments table to improve query performance

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      1/ upgrade site
      2/ no other testing can be probably done on test sites - we need a huge install to verify it helps with perf, preferably both on mysql and postgresql

      Show
      1/ upgrade site 2/ no other testing can be probably done on test sites - we need a huge install to verify it helps with perf, preferably both on mysql and postgresql
    • Affected Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      w28_MDL-33617_m24_roleenrolindex

      Description

      We have found that for enrollment plugins that are also managing role assignments that adding a combined index on the fields component, itemid and userid on the role_assignments table can improve query performance. We saw performance degradation when the role_assignments table had 1 million plus records.

      Sorry, but I'm not sure if I have a core related query as an example for improvement, but we are querying against this table for an enrollment plugin to determine role assignment updates.

      Just to avoid any confusion of the type of index, this is what we are suggesting:

      ALTER TABLE `mdl_role_assignments` ADD KEY `component_itemid_userid` (`component`,`itemid`,`userid`);

        Attachments

          Activity

            People

            Assignee:
            skodak Petr Skoda
            Reporter:
            bushido Mark Nielsen
            Integrator:
            Sam Hemelryk
            Tester:
            Michael de Raadt
            Participants:
            Component watchers:
            Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona)
            Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              10/Sep/12