Details

    • Database:
      Any
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE
    • Rank:
      2239

      Description

      Adding userid and fieldid key to increase the performence of SGBD.

      <KEY NAME="userid" TYPE="unique" FIELDS="userid" PREVIOUS="primary" NEXT="fieldid"/>
      <KEY NAME="fieldid" TYPE="unique" FIELDS="fieldid" PREVIOUS="userid"/>

        Issue Links

          Activity

          Hide
          Mine Karakaya added a comment -

          Hi, I am planning to do XML admin settings project for my SOC. I have added a patch for this bug as attachment.

          Show
          Mine Karakaya added a comment - Hi, I am planning to do XML admin settings project for my SOC. I have added a patch for this bug as attachment.
          Hide
          Colin Campbell added a comment -

          We were getting very poor performance on selects on user_info_data, so we definitely have a need for an index on the userid, but I am not sure what benefit the separate index on fieldid would provide. Do you have any queries in mind that you think the fieldid index would help? I am generally conservative about adding indexes, so if no specific benefit is expected, it should not be added.

          Also, neither index as given in the description can be unique.

          I added the following to my local/db/upgrade.php.

          if ($result && $oldversion < 2009060600)

          { $table = new XMLDBTable('user_info_data'); $index = new XMLDBIndex('userid-fieldid'); $index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('userid', 'fieldid')); $result = $result && add_index($table, $index); }

          Note that this is an index on both userid and fieldid. Although the code for user_info_data inserts implies that the combination of userid and fieldid are unique, I chose not to add such a constraint myself because the benefit would not be great and I would run the risk of the moodle.org code later breaking on my constraint. If we can get this index on both columns added to the core, however, I suggest that it be added as a unique index.

          Show
          Colin Campbell added a comment - We were getting very poor performance on selects on user_info_data, so we definitely have a need for an index on the userid, but I am not sure what benefit the separate index on fieldid would provide. Do you have any queries in mind that you think the fieldid index would help? I am generally conservative about adding indexes, so if no specific benefit is expected, it should not be added. Also, neither index as given in the description can be unique. I added the following to my local/db/upgrade.php. if ($result && $oldversion < 2009060600) { $table = new XMLDBTable('user_info_data'); $index = new XMLDBIndex('userid-fieldid'); $index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('userid', 'fieldid')); $result = $result && add_index($table, $index); } Note that this is an index on both userid and fieldid. Although the code for user_info_data inserts implies that the combination of userid and fieldid are unique, I chose not to add such a constraint myself because the benefit would not be great and I would run the risk of the moodle.org code later breaking on my constraint. If we can get this index on both columns added to the core, however, I suggest that it be added as a unique index.
          Hide
          Wen Hao Chuang added a comment -

          +1 for this. We found the same thing here at SFSU too.

          Show
          Wen Hao Chuang added a comment - +1 for this. We found the same thing here at SFSU too.
          Hide
          Justin Filip added a comment -

          +1

          At Remote Learner this problem was causing a database to completely lock up when using the browse users filters and including searching on a custom profile field.

          Show
          Justin Filip added a comment - +1 At Remote Learner this problem was causing a database to completely lock up when using the browse users filters and including searching on a custom profile field.
          Hide
          Mike Churchward added a comment -

          Just added a new issue about this - linked to this one. Although it duplicates this issue, the solution was a bit different - one index instead of two. Maybe two is better? Or maybe three?

          Show
          Mike Churchward added a comment - Just added a new issue about this - linked to this one. Although it duplicates this issue, the solution was a bit different - one index instead of two. Maybe two is better? Or maybe three?
          Hide
          Michael Aherne added a comment -

          +1

          We had the same problem with the database locking when filtering users by a profile field (although the tabletype was MyISAM at that point).

          A single index on userid & fieldid together should fix performance for code using the /user/profile/lib.php library, but I think another index on userid alone would be useful too for anyone trying to get a user's profile data in a single SQL query.

          Show
          Michael Aherne added a comment - +1 We had the same problem with the database locking when filtering users by a profile field (although the tabletype was MyISAM at that point). A single index on userid & fieldid together should fix performance for code using the /user/profile/lib.php library, but I think another index on userid alone would be useful too for anyone trying to get a user's profile data in a single SQL query.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Moving to stable backlog, randomly assigning to Andrew and raising to critical to be included in next performance-focussed sprint @ HQ.

          Some notes:

          1) Take a look to MDL-24695 (already assigned to Andrew). That shows the correct solution: one non-unique index by userid and fieldid.
          2) To implement both in 1.9.x and 2.0.x branches

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Moving to stable backlog, randomly assigning to Andrew and raising to critical to be included in next performance-focussed sprint @ HQ. Some notes: 1) Take a look to MDL-24695 (already assigned to Andrew). That shows the correct solution: one non-unique index by userid and fieldid. 2) To implement both in 1.9.x and 2.0.x branches Ciao
          Hide
          Andrew Davis added a comment -

          Ive committed a slightly modified version of the patch from MDL-24695 into my github account for Moodle 2.0.

          repo: git://github.com/andyjdavis/moodle.git
          branch: MDL-17201_index
          diff: https://github.com/andyjdavis/moodle/compare/master...MDL-17201_index

          Doing the 1.9 version next.

          Show
          Andrew Davis added a comment - Ive committed a slightly modified version of the patch from MDL-24695 into my github account for Moodle 2.0. repo: git://github.com/andyjdavis/moodle.git branch: MDL-17201 _index diff: https://github.com/andyjdavis/moodle/compare/master...MDL-17201_index Doing the 1.9 version next.
          Hide
          Andrew Davis added a comment -

          1.9 done

          repo: git://github.com/andyjdavis/moodle.git
          branch: MDL-17201_index_19
          diff: https://github.com/andyjdavis/moodle/compare/MOODLE_19_STABLE...MDL-17201_index_19

          Note when testing that, as this adds an index to a table, this involves your db being upgraded.

          Show
          Andrew Davis added a comment - 1.9 done repo: git://github.com/andyjdavis/moodle.git branch: MDL-17201 _index_19 diff: https://github.com/andyjdavis/moodle/compare/MOODLE_19_STABLE...MDL-17201_index_19 Note when testing that, as this adds an index to a table, this involves your db being upgraded.
          Hide
          Sam Hemelryk added a comment -

          Hi Andrew,
          Both look good and get my +1 on the peer-review.

          Cheers
          Sam

          Show
          Sam Hemelryk added a comment - Hi Andrew, Both look good and get my +1 on the peer-review. Cheers Sam
          Hide
          Andrew Davis added a comment -

          PULL-165 for 1.9

          PULL-164 for 2.0

          Show
          Andrew Davis added a comment - PULL-165 for 1.9 PULL-164 for 2.0
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Both PULL-164 and PULL-165 integrated and tests passing ok. Closing this a fixed. Thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - Both PULL-164 and PULL-165 integrated and tests passing ok. Closing this a fixed. Thanks!

            People

            • Votes:
              10 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: