Mysql – a better way to keep “user online”


I'm working on software for social network and I plan it to scale a lot. Actually, I'm updating timestamp column every time user clicks, to be sure they're marked as online. When they do not click for 30 seconds, they're marked as offline. So simply, my queries are

UPDATE Users SET timestamp = UNIX_TIMESTAMP WHERE uid = 1; -- UNIX_TIEMSTAMP is current unix timestamp given by PHP's time()
SELECT Name FROM Users WHERE timestamp > (UNIX_TIMESTAMP - 30); -- (UNIX_TIMESTAMP - 30) is given directly by PHP (time() - 30)

In this answer, answerer said that

Will you be updating this table continuously? I doubt that would scale

is there a better way to update user's online status that would scale? Or do you think this is fine?

Best Answer

The fastest and easiest to scale query is the query you don't run.

  • Move your session state to a cache tier like memcached or Redis.
  • Update the cached value on every user interaction, not the database value.
  • Persist to the database via a background task that periodically scans the cache for updates.