Currently, the notifier filters users by the selected channel and then iterates one-by-one to notify all of them.
However, on any given run, the vast majority of users do not have any notifications waiting for them. Iterating them one-by-one, in this case, is exceptionally stupid.
Instead, the database should first be asked for a list of users that have notifications waiting for them. Only this list of users should then be iterated.
Requirements:
The query should not be slow. It can take probably about 100x the duration of the query for a single user and still be faster overall, but a faster query will have greater benefits.
The query must return all users that have notifications waiting, otherwise notifications will be missed.
'Notifications waiting' is defined as 'if a notification digest were to be compiled for this user right now, it would contain at least one notification'
The query should not return users that do not have notifications waiting. It should return as few as possible false positives, but the only downside is time wasted on these users.
Counterpoint: this might not be possible, because the bottom bounding timestamp for the post search for each user varies between users - it's the timestamp at which they were last notified. There's not a catch-all query that could answer this question for all users regardless of timestamp.
Countercounterpoint: a given user's last notification timestamp is recorded in the database, and I can just use that. This will possibly need a subquery to retrieve that data for immediate use, and that'll be real slow, but I don't care, because it's competing against running main query 400 times in a row and that's not a very high bar.
Manual subscriptions are going to be difficult.
I don't need to worry about manual unsubscriptions, because it doesn't matter if there are some users in the resulting list who shouldn't be. I do need to worry about manual subscriptions, because the list must contain everyone that it needs to.
Currently, the notifier filters users by the selected channel and then iterates one-by-one to notify all of them.
However, on any given run, the vast majority of users do not have any notifications waiting for them. Iterating them one-by-one, in this case, is exceptionally stupid.
Instead, the database should first be asked for a list of users that have notifications waiting for them. Only this list of users should then be iterated.
Requirements:
Counterpoint: this might not be possible, because the bottom bounding timestamp for the post search for each user varies between users - it's the timestamp at which they were last notified. There's not a catch-all query that could answer this question for all users regardless of timestamp.
Countercounterpoint: a given user's last notification timestamp is recorded in the database, and I can just use that. This will possibly need a subquery to retrieve that data for immediate use, and that'll be real slow, but I don't care, because it's competing against running main query 400 times in a row and that's not a very high bar.
Manual subscriptions are going to be difficult.
I don't need to worry about manual unsubscriptions, because it doesn't matter if there are some users in the resulting list who shouldn't be. I do need to worry about manual subscriptions, because the list must contain everyone that it needs to.