Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm currently working on a social network software written in PHP & MySQL, and it's all pretty fine, but one thing I'm stuck about is online friends. My tables looks following

Users

+-----+------------+---------------------------------------------------------------+
| uid | timestamp  | profile_img                                                   |
+-----+------------+---------------------------------------------------------------+
|   1 | 1322858590 | /photos/356a192b7b/profile.jpg                                |
|   2 |   13700000 | /photos/356a192b7/profile.jpg                                 |
+-----+------------+---------------------------------------------------------------+

Friends

+------+------+
| uid1 | uid2 |
+------+------+
|    1 |    2 |
+------+------+

now I want to select all online friends (online = timestamp > current time - 30 seconds)

My try to create a query to select online friends is

SELECT  f.uid1 as friend1,
    f.uid2 as friend2,
    u1.profile_img profile_img1,
    u2.profile_img profile_img2
FROM Friends f
INNER JOIN Users u1 ON u1.uid = f.uid1
INNER JOIN Users u2 ON u2.uid = f.uid2
WHERE 
    f.uid1 = 1 OR 
    f.uid2 = 1;

I have no idea how to compose the timestamp check, considering that

  • friend1 can be me, but it can also be my friend.
  • friend2 can be me, but it can also be my friend.

Is there a solution for this or should I think a bit more about database schema?

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

Personally, I would have friends be a one-way association; this would double the length of your friends table but eliminate many places where you would need to duplicate logic depending on whether the current user was listed on the left or the right of each entry.

+------+------+
| uid1 | uid2 |
+------+------+
|    1 |    2 |
|    2 |    1 |
+------+------+

Given a choice between saving RAM/hard drive space, and simpler business logic, the choice should almost always fall to simpler rather than smaller. I suspect that most use-cases will have this performing better as well.

share|improve this answer
    
That's exactly what I wanted to avoid –  genesis Dec 3 '11 at 7:20
    
@genesis - Why? –  Nick Chammas Dec 3 '11 at 17:53
    
@NickChammas: this would double the length of your friends –  genesis Dec 3 '11 at 18:07
    
@genesis - Correct, but as Myrddin noted, the aim would be to sacrifice storage space for simpler logic and perhaps even better performance (due to having better execution plans as a result of the simpler logic). –  Nick Chammas Dec 3 '11 at 18:30
    
SQL optimization that databases perform works better the simpler and more predictable your query; less common join methods, such as @mrdenny suggested, make it difficult for the database server to optimize well. Also... this is a tiny table. Don't worry about bloating it. Even with billions of friends, indexing will make queries against this table a snap. –  Myrddin Emrys Dec 3 '11 at 20:36
add comment

I agree with Myrddin that in the long run one-way associations are probably better. Could use triggers to force them to always in pairs.

Barring that: the OR in your own query is not optimized by MySQL and will make it very slow, let alone after adding any timestamp clauses.

Also: I don't understand the timestamp. Is that the time the user was last logged in? Will you be updating this table continuously? I doubt that would scale. Also, the test should then be: (online = timestamp > current time - 30 seconds) correct?

Anyway, you can get rid of the OR and make the timestamp check easier by using a UNION. Note that I assume the timestamp field is a DATETIME, change it accordingly if it's an epoch serial or whatever.

SELECT  f.uid1 as friend1,
    f.uid2 as friend2,
    u1.profile_img profile_img1,
    u2.profile_img profile_img2
FROM Friends f
INNER JOIN Users u1 ON u1.uid = f.uid1
INNER JOIN Users u2 ON u2.uid = f.uid2
WHERE 
    f.uid1 = 1 AND (u2.timestamp > NOW() - INTERVAL 30 SECOND) 

UNION ALL

SELECT  f.uid1 as friend1,
    f.uid2 as friend2,
    u1.profile_img profile_img1,
    u2.profile_img profile_img2
FROM Friends f
INNER JOIN Users u1 ON u1.uid = f.uid1
INNER JOIN Users u2 ON u2.uid = f.uid2
WHERE 
    f.uid2 = 1 AND (u1.timestamp > NOW() - INTERVAL 30 SECOND) 
;

How do you actually make sure friends are not linked twice? (1, 10) and (10, 1) for example. Do you have triggers in place? A TRIGGER to always force the lowest id to be the first user for example?

share|improve this answer
    
the OR in your own query is not optimized by MySQL and will make it very slow Can you tell me why do you think so? I select all friends with this query, but not sure why would it be a trouble –  genesis Dec 3 '11 at 17:23
    
Also: I don't understand the timestamp. Is that the time the user was last logged in? time of the last user click, updated every click. Also, the test should then be correct, thanks for warning! ` Will you be updating this table continuously? I doubt that would scale` how would you do it then? –  genesis Dec 3 '11 at 17:26
    
How do you actually make sure friends are not linked twice? (1, 10) and (10, 1) for example. ... Currently "add friend" isn't in place but I'd probably use SELECT COUNT(*) FROM Friends WHERE (uid1 = 1 AND uid2 = 2) OR (uid1 = 2 AND uid2 = 2). Timestamp is unix time –  genesis Dec 3 '11 at 17:40
    
@genesis 1) OR will not be optimized because MySQL can only use one index at a time: you're ORring two fields. MySQL may still do some caching and some smarts depending on the version, but it's not a single simple index lookup. 2) Lots of updates. Not sure how to handle that, but some sort of caching layer sounds like a good plan. 3) That would work. But I would expect a solution with INSERT and UPDATE TRIGGERS more efficient. Let the TRIGGER ensure that uid1 is always smaller than uid2. Then put a UNIQUE index on (uid1, uid2) and let MySQL handle it. –  Jannes Dec 13 '11 at 17:01
add comment

Something like this should work. Not sure how well MySQL will take the OR statement though.

SELECT  f.uid1 as friend1,
    f.uid2 as friend2,
    u1.profile_img profile_img1,
    u2.profile_img profile_img2
FROM Friends f
INNER JOIN Users u1 ON u1.uid = f.uid1 OR u1.uid = f.uid2
WHERE u1.uid = 1
and u1.Timestamp < dateadd(ss, -30, current_time)
share|improve this answer
    
That looks fine, but there's missing "u1" alias. I have no idea how could I join it not to break your logic –  genesis Dec 3 '11 at 17:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.