What is the best way to count the number of unread messages?

Question

Database design

Dialogs can have more than 2 users. Counting the number of unread messages is the most resource intensive task in my opinion.

I think between 2 implementation options:

1. We store a counter of unread messages. Each counter is tied to a specific user and dialog. The problem with this approach is that when sending a message, you need to increment the counters of all participants in the dialogue. Messages can be sent quite often, which will lead to a huge number of requests to the database for updates.

2. We store the timestamp of the last reading of the dialog by the user, again tied to a specific dialog and user. In this case, we don't need to make update requests, but when displaying dialogs, we need to execute one request per dialog in order to count the number of messages sent since the user last read the dialog.

In the first case, we have a lot of frequent requests to update records, in the second, infrequent, but search queries to a fairly large message table.

Both options seem far from ideal to me, is there something better?

Answer 1

The word temporary means "impermanent." And you want to store the timestamp . A programmer, like no one else, should understand how important it is to clearly express your thoughts.
About "infrequent" requests I did not understand. 20 years ago, when the only way to update the status of a dialog was to refresh the forum page in the browser, that might have worked. And now, when the user is waiting for the information to be updated without taking any action, but simply looking at the screen, and even in another application, I don’t understand how this should work. When exactly should an application make its infrequent selects?
The issue of frequent updates is solved by using the service designed for this. For example, radish.
As a fallback, you can also store a timestamp, in case the necessary key is not in the radish - then the code will quickly calculate and update the counter