CodeIgniter Forums
Table fields for "friends" ... more of a MySQL Question - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Table fields for "friends" ... more of a MySQL Question (/showthread.php?tid=6593)



Table fields for "friends" ... more of a MySQL Question - El Forum - 03-04-2008

[eluser]jonnyjon[/eluser]
Hi guys,
I have a friends table but I'm debated how to lay it out and use it.

Currently. this is how it looks:

=======================================================
| id | user_id | user_id_2 | is_approved | is_blocked |
=======================================================

So if user 1 and 2 are friends it would require 2 records for that friendship, one record to say user 1 is friends with 2 and another to say user 2 is friends with 1.

==============================
| 1 | 1 | 2 | true | false |
| 2 | 2 | 1 | true | false |
==============================

There must be a more efficient way to do this?


Table fields for "friends" ... more of a MySQL Question - El Forum - 03-05-2008

[eluser]rweston[/eluser]
Why do you think you need 2?

If i'm thinking what you're thinking, all you'll need to find the 'friends' of one user is something along the lines of:

SELECT * FROM friends WHERE user_id = 1 UNION SELECT * FROM friends WHERE user_id_2 = 1;

Will that give you what you want?


Table fields for "friends" ... more of a MySQL Question - El Forum - 03-05-2008

[eluser]webthink[/eluser]
Well it really depends on the needs of your application. You could do it with a single record.
if it's something like facebook user_id_1 is the id of the user requesting freindship, user_id_2 is the recipient. is_approved = 1 if the recipient accepts the friendship. Is blocked I would have a seperate table for as people can probably block one another without being friends.
so if I'm coming up with a list of friends for user 88 you search where (user_id = 88 OR user_id_2 = 88) AND is_approved = 1;


Table fields for "friends" ... more of a MySQL Question - El Forum - 03-05-2008

[eluser]webthink[/eluser]
Hmmm wonder if the UNION would be faster than the OR. My guess would be that the OR would be faster if you index user_1 and user_2 worth doing some benchmarking though Smile


Table fields for "friends" ... more of a MySQL Question - El Forum - 03-05-2008

[eluser]rweston[/eluser]
[quote author="webthink" date="1204723954"]Hmmm wonder if the UNION would be faster than the OR. My guess would be that the OR would be faster if you index user_1 and user_2 worth doing some benchmarking though Smile[/quote]

mySQL unions perform very poorly [based on my limited experience, and the fact that i may have been doing something wrong] so i'd have to go with the OR solution.


Table fields for "friends" ... more of a MySQL Question - El Forum - 03-05-2008

[eluser]Josh Giese[/eluser]
I have a social network with friendships. One table, using the union method works great for me.


Table fields for "friends" ... more of a MySQL Question - El Forum - 03-05-2008

[eluser]rweston[/eluser]
[quote author="Josh Giese" date="1204726028"]I have a social network with friendships. One table, using the union method works great for me.[/quote]

I should have been clear. For this application, the Union VS OR probably doesnt make a big difference, but i tried SELECT A,B,C FROM Table1 WHERE A Like 'XY%';

where Table1 was a View which was a union of 2 base tables and of course, what was expected was that the underlying indices were to have been used, but that did not seem to be the case.


Table fields for "friends" ... more of a MySQL Question - El Forum - 03-05-2008

[eluser]webthink[/eluser]
It seems we're losing sight of the issue a bit but rweston, I seem to remember something to do with LIKE statements not being able to use indicies. You might want to look into that.
Either way I think for jonnyjon's case the OR solution I provided above is probably the best way to go. That is after all what OR was designed for. UNIONS are typically used to join 2 tables of unrelated data which isn't really what we're dealing with here.


Table fields for "friends" ... more of a MySQL Question - El Forum - 03-05-2008

[eluser]jonnyjon[/eluser]
Thanks for all the great replies guys. Good to hear I can use only 1 record per association.


Table fields for "friends" ... more of a MySQL Question - El Forum - 03-05-2008

[eluser]rweston[/eluser]
[quote author="webthink" date="1204729489"]It seems we're losing sight of the issue a bit but rweston, I seem to remember something to do with LIKE statements not being able to use indicies. You might want to look into that.
Either way I think for jonnyjon's case the OR solution I provided above is probably the best way to go. That is after all what OR was designed for. UNIONS are typically used to join 2 tables of unrelated data which isn't really what we're dealing with here.[/quote]

[thumbs up], but when using the same queries against the base table, the query time was acceptable vs use against the view. Anyway, good point though; maybe i should start my own thread about the experience. Smile

[quote author="jonnyjon" date="1204747782"]Thanks for all the great replies guys. Good to hear I can use only 1 record per association.[/quote]

[thumbs up]