Thanks for your answer Mr Garrison.
yes I thought about displaying all the users and page the result but for what I want to do, it’s not really ideal.
I don’t want a user to scroll through a hundreds of his friends in search of someone online to play with.
That would be a waste of time… especially for someone with 1000 or more friends whose only friend online is at the very bottom of the list 
Regarding scanning table, it’s really not an option for me, would be a waste of time, performance and money…
Let’s say I go for the SQL option:
So basically I’d have 2 tables Users(user_id, last_active_date, status) and Friends(user_id, friend_id)
On the Users table I can create a composite index on “last_active_date” and “status” (away, offline, online)
last_active_date will be updated in batch at the app level to optimize server round trip.
Regarding the Friends table I would store only one side of the relationship
To fetch the online friends of a user joining I could do something like:
select user_id, friend_id from Friends f
inner joins Users u on (f.user_id = u.user_id or f.friend_id=u.user_id)
where u.user_id = :user_id
limit 100
or may be I could even do that in batch rather for a single user
where u.user_id in (:user_ids)
Is there anything else I can look at to improve performance, bearing in mind, that table will be massive at some point ? Partitioning ? Materialized views ?
I heard Materialized views do not scale very well, and about partitioning the Friends table ? would that really benefit in anything ?
At the app level to improve even more performance, once the first users joining start fetching their friends I can store in memory their relationship for each of them:
Basically if users 1 ans 2 join and both have friendship with user 3, when user 3 joins, I can first check the ets tables where already fetched relationships are stored, rather than directly querying the DB.
The idea is to constantly take advantage of the relationship already fetched by other users, so that I can display result faster and have a kind of an in memory “online” representation of users friendship.
the other thing is would broadway/genstage benefits in that scenario to not overload the SQL DB?
Is that too naïve kind of approach ?
