I am building a business networking site. Each user will have own network of contacts, like on LinkedIn. So there will be his direct contacts (1st level), contacts of his contacts (2nd level) and 3rd level contacts.
I am not sure what would be the best way to get information about entire user network to 3rd degree level.
I have tried many things:
-I created for each friend connection a new row in mysql database (id | userid | friendid). Than did query SELECT * FROM network WHERE userid=$userid, than with PHP while loop and query for each friend to get all contacts of user friends and than again the same to get 3rd level.
-all 1st level friends in 1 field as text (separate them with : and than use PHP explode). Than do similar same to get 2nd and 3rd level.
-again for each 1st level friend connection a separated row but all 2nd and 3rd contacts into 1 field as text (id | userid | friendid | networkoffriend (separate 2nd level with ":" and 3rd level with ";" ).
All options are far too slow. I have no idea how LinkedIn, Xing and other business social networking sites are doing this because if each user has 100 friends than there is 1mio users in 3rd level.
Has anybody any idea?
Thanks a lot!
Or you could show those 3rd level contacts who are friends with 2 of more 2nd level contacts.
i'm not even going to try to guess how large professional networking sites store their data
for someone who is just starting out, however, the "(userid | friendid)" model works fine
rather than doing a query, then looping over the results and doing a subsequent query for each row, you should be doing a self-join
and yes, if each user has 100 friends, the network can get very large, so you will have to give some thought to exactly what kind of results are practical to show to a user -- and showing all 1,000,000 3rd-level contacts doesn't sound very practical
Thank you, I will try also self-join. Is there any other way I should try?
I mentioned 1,000,000 3rd-level contacts because this will be related also to search and I need to display a number of results. But I am not sure if it is even possible to realize this with basic solutions, if not I will use only 2nd level contacts.
I tried the below code and this took the longest time. Actually it didn't stop loading. Did I make anything wrong?
mysql_query("SELECT friendid FROM friends WHERE userid IN ( SELECT friendid FROM friends WHERE userid=203)") or die(mysql_error());
did you declare any indexes?
no, I am not sure what should be indexed.
okay, please do a SHOW CREATE TABLE for the table