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

Notifications table has no index for the useridfrom column, and it has significant impact on performance

    XMLWordPrintable

Details

    Description

      The notifications table has two fields useridto and useridfrom. Both are used in SQL queries, including in the privacy API where we often use things like "useridfrom = ? OR useridto = ?"

      The field useridto is defined as a foreign key so it has an index created for it implicitly.

      The useridfrom would normally be a foreign key to the users table, too. But it can also contain some extra negative values with special semantics (-10 or -20 for no-reply or support user). Therefore we can't make it a foreign key and the index must be created explicitly.

      But it was not so there is no index. This can lead to serious performance issues. In case of moodle.org with millions of notifications records, it effectively made the query stuck (especially in combination with MDL-64400).

      Attachments

        1. integration-35.png
          655 kB
          Bas Brands
        2. integration-36.png
          652 kB
          Bas Brands
        3. integration-master.png
          646 kB
          Bas Brands

        Issue Links

          Activity

            People

              mudrd8mz David Mudrák (@mudrd8mz)
              mudrd8mz David Mudrák (@mudrd8mz)
              Jun Pataleta Jun Pataleta
              Sara Arjona (@sarjona) Sara Arjona (@sarjona)
              Bas Brands Bas Brands
              Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Clockify

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.