Mysql – a better way to keep “user online”

MySQLscalability

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.

Related Solutions

MySQL: A better way to write this query

Note: 1 + 1 + NULL = NULL

For this reason I'd change one part...

SUM(CASE
      WHEN e.oiPost  = 1 THEN 1
      WHEN e.oiPhone = 1 THEN 1
      WHEN e.oiEmail = 1 THEN 1
                         ELSE 0
    END)                                  AS opt_ins

Why all the records in the submission table are being processed, I have no idea. How many rows do you actually have that are submitted > '2012-04-01'?

Mysql – what better way to multiple ON conditions

There's nothing wrong with the query regarding the joins and how to find the values (except some details). It should be efficient if there are appropriate indexes. There is nothing horrible or "unperformatic" about it.

Things that need addressing:

Syntax issues:
- use the correct columns for joining (c.accountManagerID, c.attendantID, u1.userID, etc).
- there is noLEFT INNER JOIN. There are LEFT OUTER JOIN and INNER JOIN (which can be written respectively as LEFT JOIN and JOIN).
- add the table's name or alias in each column reference (c.customerID, not customerID). This won't always cause an error but when you'll have columns from multiple tables with same name, it helps avoiding them.
- don't use single quotes for aliases, only for string literals. For aliases, remove them or use back quotes (`alias`) if necessary. MySQL allows it but it's confusing and even the official documentation suggests to avoid using single quotes for aliases.

Logic issues:

use LEFT JOIN if the two columns (accountManagerID and attendantID) are nullable (i.e. if there are customers with no manager or no attendant assigned). Otherwise, with INNER joins, you will not see such customers in the results.

SELECT c.customerID        AS ID
     , c.customerName      AS Customer
     , u1.accountManagerID AS Manager
     , u2.attendantID      AS Attendant
FROM Customers c 
    LEFT JOIN Users u1 ON u1.userID = c.accountManagerID 
    LEFT JOIN Users u2 ON u2.userID = c.attendantID ;

Best Answer

Related Solutions

MySQL: A better way to write this query

Mysql – what better way to multiple ON conditions

Related Question