User notifications table allowing duplicate entries

According to the report made here:
https://www.phpbb.com/community/viewtopic.php?f=64&t=2401681
It is possible to receive duplicate notifications.

After debugging and researching this issue is happening because the user_notifications table allows for duplicate entries of each notification type and method per user. Not entirely sure how this happened, but one possible solution going forward is to add a constraint wherein the item_type, user_id, and method columns need to be unique.

The query needed to find the existing duplicate data is as follows:

SELECT un.user_id, un.item_type, un.method

FROM phpbb_user_notifications un

INNER JOIN (SELECT user_id, item_type, method

            FROM phpbb_user_notifications

            GROUP BY user_id, item_type, method

            HAVING COUNT(item_type) > 1) AS undups

ON un.user_id = undups.user_id;

This returns 184 rows on phpbb.com but will be needed in order to create a migration to correct the data.

While running the above query it was also noted that it is EXTREMELY slow. The notification settings page is also responding very slow as well on load and on saving the form. Upon closer inspection there are no indexes in the user_notifications table. I would recommend adding the following indexes:

ALTER TABLE phpbb_user_notifications

   ADD KEY `user_id` (`user_id`),

   ADD KEY `user_id_item_id` (`user_id`, `item_id`),

   ADD KEY `item_type_item_id_user_id_method` (`item_type`, `item_id`, `user_id`, `method`),

   ADD KEY `user_id_item_type_item_id` (`user_id`, `item_type`, `item_id`);

This is based on how the data is being retrieved in several portions of the core.