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
          integration-35.png
          655 kB
        2. integration-36.png
          integration-36.png
          652 kB
        3. integration-master.png
          integration-master.png
          646 kB

          Issue Links

            Activity

              People

              Assignee:
              mudrd8mz David Mudrák (@mudrd8mz)
              Reporter:
              mudrd8mz David Mudrák (@mudrd8mz)
              Peer reviewer:
              Jun Pataleta
              Integrator:
              Sara Arjona (@sarjona)
              Tester:
              Bas Brands
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Víctor Déniz Falcón, Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                11/Mar/19