I'd simply like to locate a database structure in MySQL to obtain all customers buddies of buddies and also the corresponding query to retrieve them. (friend links are bi-directional)

I've discovered a few posts associated with that, but my problem is the performance:

Structure 1

Many posts advise a structure where you've got a table by which each row signifies a friendship link e.g:

    CREATE TABLE `friends` (
    `user_id` int(10) unsigned NOT NULL,
    `friend_id` int(10) unsigned NOT NULL,

saying the consumer '1' has three friend '2','3','4' and user '2' has two friend '1','5' . Your friend table would seem like this:

    user_id    |    friend_id
    1          |    2
    1          |    3
    1          |    4
    2          |    1
    2          |    5

buddies of buddies query: How you can choose buddies of buddies is visible here SQL to get friends AND friends of friends of a user. The rsult from the query for user '1' should really give (1,2,3,4,5)

My concern: The typical facebook-user has about 140 buddies. Frequent customers may have much more. Basically have 20.000 customers this can finish up in a minimum of 3million rows.

Structure 2

Basically can use a structure such as this:

    CREATE TABLE `friends` (
    `user_id` int(10) unsigned NOT NULL,
    `friend_1` int(10) unsigned NOT NULL,
    `friend_2` int(10) unsigned NOT NULL,
    `friend_3` int(10) unsigned NOT NULL,
    `friend_4` int(10) unsigned NOT NULL,

My table would seem like this (taking example previously mentioned):

    user_id  |  friend_1  |  friend_2  |  friend_3  |  ...
    1        |  2         |  3         |  4         |
    2        |  1         |  5         |            |...

Now I've only 20.000 rows.

buddies of buddies query: To choose user buddies of buddies I attempted

    Select * FROM friends as a
    WHERE a.user_id 
    IN (
        SELECT * FROM friends AS b
        WHERE b.user_id = '1'

however i have an error "#1241 - Operand should contain 1 column(s) ". I believe the issue is, the sub-selection passes a row, not really a column?


I think you'll understand my concern. I'd be really really happy about any input to those questions

1) look for a query that returns all buddies of buddies for any specified user in structure 2?

2) Which structure enables me to come back buddies of buddies faster? In structure 2 I believe the "join row with column" might be slow, if it is even possible to utilize a join here. Appreciate any suggestions. Should you could think about every other structures, maybe benefiting from the little-world-network-type I'd gladly hear them.


I'd say you need to make use of the first structure. It's more flexible for me. My solution for that query will be a simple sub-query, such as this:

SELECT friend_id FROM friends WHERE user_id IN (

       SELECT friend_id FROM friends WHERE user_id='$USER_ID'


EDIT: Sorry I simply awoke and recognized after posting an answer this wasn't whatsoever that which you were searching for. Sry.

Avoid using "Structure 2" you can't produce a column for those customers if just 1 user have let us say 100 buddies (what abaout 10K buddies or even more ?) it leads to low performance, for structure 1 you are able to perform a simple join towards the same table:

select u.user_id, f.friend_id 
from friends as u 
  inner join friends as f
    on (u.friend_id=f.friend_id);


you are error #1241 means that you employ * within the subselect and also the table returns a lot more than 1 column, your subquery should return only one colums (no mather the number of rows), so improve your "*" having a "user_id" (without quotes)

Solution 1 isn't just faster it's flexible, I do not recomand a subquery for any simple choose such as this, just join exactly the same table (it's considerably faster than the usual subselect).

Solution 2 for me isn't a solution whatsoever, it isn't flexible, it reduced, it uses more room on HD, more posts means less performance in mysql. How will you index this type of factor? And just how are you able to choose by friend_id not by user_id, you appear in each and every column for your friend_id ?