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

Comments table in db has no indexes

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 3.4.3, 3.5, 3.6
    • Fix Version/s: 3.4.4, 3.5.1
    • Component/s: Comments, Privacy
    • Labels:
    • Testing Instructions:
      Hide

      Note

      Testing requires low level access to the database.

      Upgrade test

      1. Upgrade an existing Moodle site to a patched version.
      2. TEST: Make sure the indexes were created during the upgrade.

      Fresh install test

      1. Install a fresh new site from the patched code.
      2. TEST: Make sure the indexes were created during the installation.

      Hints

      On MySQL, you can use

      mysql> SHOW INDEX FROM mdl_comments;
      

      On PostgreSQL you can use

      # \d mdl_comments;
      

      In both case, you should confirm that

      • you can see the index over the userid field.
      • you can see the composite index over the fields contextid, commentarea, itemid (in this order).
      Show
      Note Testing requires low level access to the database. Upgrade test Upgrade an existing Moodle site to a patched version. TEST: Make sure the indexes were created during the upgrade. Fresh install test Install a fresh new site from the patched code. TEST: Make sure the indexes were created during the installation. Hints On MySQL, you can use mysql> SHOW INDEX FROM mdl_comments; On PostgreSQL you can use # \d mdl_comments; In both case, you should confirm that you can see the index over the userid field. you can see the composite index over the fields contextid, commentarea, itemid (in this order).
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_34_STABLE, MOODLE_35_STABLE, MOODLE_36_STABLE
    • Fixed Branches:
      MOODLE_34_STABLE, MOODLE_35_STABLE
    • Pull from Repository:
    • Pull 3.5 Branch:
      MDL-62621-35-ixcomment
    • Pull Master Branch:
      MDL-62621-master-ixcomment

      Description

      causes major performance issue, especially in privacy api

      I suggest to add two indexes:
      1. contextid, commentarea, itemid
      2. userid, commentarea, component

      first index will help existing queries in comments api, second index will help queries in privacy api

        Attachments

          Issue Links

            Activity

              People

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

                Dates

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