PHP: Most Efficient Way to Track if a User is Online?

Question

I'm developing a project of mine with scalability in mind and I've come to a crossroad. On my website I would like to detect if a user is online or not. And I can't quite think of the best way to handle this. The way I was thinking would be something along these lines(in psuedocode):

// SQL user table:
user {
  "name": "blah blah",
  "email": "[email protected]",
  "online": false
}

So whenever the user logs in I could update his online column to true. However that would eventually lead to SQL queries happening every time a user logs in and if it happens that I get say, 10 logins per second, well, that's a lot of queries happening. Another way I figured I could do the same thing but in a different table:

// Activity table:
activity {
  "user_id": 2,
  "online": true
}

For some reason I believe that would lead to less memory consumption because of the separation from the user table. However I'm not sure if it would have any actual effect on performance.

So if you could bless me with your insight I would be more then grateful, thank you.

Answer 1

Generally speaking it's a common practice to add a column to the users table to store the lastActivity time. Anytime the user logs in, or accesses a page, store the current time in that field. If you want to know whether or not they are online, see if the last recorded time is within a certain window - say, five minutes. You can query all rows to see how many users are currently online as a result.

I wouldn't be too worried about running queries every few seconds - your server can handle it (assuming these are well-written and not very verbose).