How to correctly implement new message notifications so that mysql b. did not fall?

Question

MySQL

Hey!
I have 2 ideas so far on how to do this, the first one (I feel that it’s bad):
1) Just do Select count(*) from messages where user_id = 1 AND status = NULL
That is, every time run through the entire table and count how many new messages - I think per 500 people online b. e. will refuse.
2) Make a separate table, where one record is one user_id. And in this entry there will be a mess_ids field - where the id of unread messages will be listed separated by commas. The table will be updated when another user sends a message to the current user. Anything faster than select count(*)
Can you tell me other solutions in php and mysql. How to organize table logic and work logic? Without third party solutions like reactPHP etc.?

Answer 1

Both solutions are the same.
In the user table, which you probably already have, make a numeric field unread_messages_count with the number of unread messages, which is updated when someone writes a new message to the user or the user himself reads a previously unread message.
In the messages table, make an is_read (true/false) field on which to hang an index, so you can determine which messages are not currently read.
Keeping a comma-separated list of IDs in one field is inefficient and pointless.

Answer 2

Here, for some reason, everyone slid into discussions of counters.
The counter is really good to do in redis, especially if this counter changes frequently.
Although if this is a counter of simply unread messages from the user, it is quite possible to store it and as written by the author according to the 2nd option.
But the title of the author's question, as I understand it, is in a different one - "how to implement a notification ".
Probably it is meant that heaps of web pages will send packs of ajax requests.
With such a scheme, the bottleneck will not be sql, but very much even php.
The correct decision is to raise any comet server.
You can use php (slow and crooked), you can use node.js + socket.io (fast and good)

Answer 3

the second solution is very standard, only it is better not to put it in a separate table, but in any key=>value storage, for example memcaced or redis, where the key is the user id.
When changing data, simply reset the key.

Answer 4

Select count(*) from messages where user_id = 1 AND status = NULL

Quite a normal solution. You just need an index by user_id + status. When the database starts to fail then you will think about optimization (memcached, that's all). Premature optimization is the root of most problems.

Answer 5

Make a "counter" (at least in the user's table, at least in kv storage), which is incremented when a new message arrives and decremented when reading it.
do not do it this way

Answer 6

Agree with the comment above. nodeJS + socket.io + possibly redis (if you need something more complex).
Very useful if the user uses multiple clients. I somehow tried to reinvent the wheel on mysql + ajax requests, but there were a lot of difficulties with getting the latest updates. Well nodeJS + socket.io saved me! I spent two times less time on development than on the invention of the bicycle with mysql.