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

Comments table in db has no indexes

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 3.4.3, 3.5, 3.6
    • 3.4.4, 3.5.1
    • Comments, Privacy
    • MOODLE_34_STABLE, MOODLE_35_STABLE, MOODLE_36_STABLE
    • MOODLE_34_STABLE, MOODLE_35_STABLE
    • MDL-62621-master-ixcomment
    • Easy
    • 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).

    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

          has a non-specific relationship to

          Improvement - An enhancement to an existing Moodle feature. MDL-75752 Performance problem from a query in in mdl_comments

          • Minor - Minor loss of function where workaround is possible
          • Closed
          will help resolve

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDLSITE-5469 moodle.org problems

          • High - Customer/Partner Issue, GDPR removal, priority Features
          • Resolved

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-62602 Data requests stuck on pre-processing

          • Minor - Minor loss of function where workaround is possible
          • Closed

          Activity

            People

              mudrd8mz David Mudrák (@mudrd8mz)
              marina Marina Glancy
              Jake Dallimore Jake Dallimore
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Ryan Wyllie Ryan Wyllie
              Amaia Anabitarte, Carlos Escobedo, Laurent David, Mikel Martín Corrales, Sabina Abellan, Sara Arjona (@sarjona), Andrew Lyons, Adrian Greeve, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                9/Jul/18