PHP/MYSQL help for giving notifications to all users(followers) even millions of them

Question

I want to make a system in PHP/MYSQL through which users of a site can follow/unfollow other users of the same site and when a user do some activity then that user's all followers should get a notification.

I know that I can create a seperate db tables called notifications and insert row in it for each user who needs to be notified separably like Facebook did, but i will not restrict users (i.e. on Facebook you cannot have more then 5000 friends), on my site users can follow any amount of users they wish, and if some user have 1 million followers then on my server his each and every activity will let my server down.

So how to accomplish this task, what kind of design do I need to use for my Mysql database, in what way do I need to call those rows so that i can send notifications to all followers of the user and the server also never let down.

Answer 1

Use a combination of your suggestion and the answer and comments from Oswald.

The author creates an update, which will then add records to the notifications table(s). If the author has 10,000 followers, then there are 10,000 entries. This will become quite large, and optimization, caching, etc... will need to be used.

The follower then connects at some interval (via a client similar to Facebook web or hand held) and pulls their notifications. In this case, I get notifications from my last visit or all my authors. Again, this may be 10,000 rows as you indicated, but with better keys, the retrieval will be quick, since you get notifications for the follower only (their ID). Once received, delete the pending notifications.

The second alternative would be to modify the notifications to simply have an entry for the author, and the post, and a date. The follower connects, queries for new postings since his last search, and then filter them by author. Try to remove as many records as possible in the first query, then attempt the filter as a second query or cached reference search. Do not try to link both queries as you will kill the database server. Using keys/indexes to get the smallest set first.

If the follower has a few authors, query for author updates first, then after the date. Otherwise, search after the date then by authors.

Answer 2

The follower pulls the notifications from the users he follows. That way, a notification is a single row in a table. The rational is, that while I might have millions of followers, the number of people that I follow is quite small (probably less than 1000).

Also: think about a caching strategy.