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


      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).

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

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


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