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:
                Jake Dallimore, Jun Pataleta, Ryan Wyllie, Jake Dallimore, Jun Pataleta, Ryan Wyllie, Matteo Scaramuccia, Jake Dallimore, Jun Pataleta, Ryan Wyllie
              • Votes:
                2 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

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