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