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

              Assignee:
              mudrd8mz David Mudrák (@mudrd8mz)
              Reporter:
              marina Marina Glancy
              Peer reviewer:
              Jake Dallimore
              Integrator:
              Eloy Lafuente (stronk7)
              Tester:
              Ryan Wyllie
              Participants:
              Component watchers:
              Jake Dallimore, Jun Pataleta, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

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