new comments notification implementation for a web site

I've got a website where people can make posts and comment on them. I want to make a new comment notification system, so when a user logs in he can see number of new comments to his posts.

My idea is to add a 'rea开发者_运维知识库d' table, where I will store user_id and comment_id (means user_id read comment_id). But this can make some performance issue, when table will grow big.

What's the best way to implement this?

One way to achieve that is just to save a created date for post and comment, and a "last visit" date for the user (or "last time the user click on the "show me new post" link).

Then, you just need to get all the post and comment that have a newer date than the one you find in the user table.

How about just storing the user and comment IDs for unread comments instead? Then when the user reads the comment, you can delete that row from the table.

Wouldn't it be better to store the post time in the notifications table and then look for the user's last activity (again storing the value in the users table). Then you could do a simple query:

$lastActivityTime = User->GetLastActivity();
$result = mysql_num_rows(mysql_query("SELECT COUNT(`id`) FROM `notifications` WHERE `postTime` > '$lastActivityTime'"));

Good luck!

You can choose a different storage than mySQL for this data (Like : MongoDB).

Or you can make pub/sub implementation for notifying users. (With using pubsub you dont need to store data. Just notify)