MySQL restores the structure of friends of friends and friends

Question

I just wanted to find the database structure in MySQL to get all friends of friends of friends and the corresponding query to get them. (friends links are bidirectional)

I found a couple of posts related to this, but performance worries me:

Structure 1

Many posts offer a structure in which you have a table in which each row represents a friendship link, for example:

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

saying that user '1' has three friends '2', '3', '4', and user '2' has two friends '1', '5'. Your friend’s table will look like this:

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

: SQL, , , '1' (1,2,3,4,5)

: fb- 140 . . 20.000 , 3 .

2

​​:

    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,
    ....
    )

( ):

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

20 000 .

: ,

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

"# 1241 - 1 ". , , , ?

, . .

1) , 2?

2) ? 2 , "join row with column" , . . - , , , .

!!

Answer 1

. , - .

:

(
    select friend_id 
    from friends 
    where user_id = 1
) union (
    select distinct ff.friend_id 
    from 
        friends f
        join friends ff on ff.user_id = f.friend_id
    where f.user_id = 1
)

:

ALTER TABLE `friends` ADD UNIQUE INDEX `friends_idx` (`user_id` ASC, `friend_id` ASC);
ALTER TABLE `friends` ADD INDEX `friends_user_id_idx` (`user_id` ASC);

Answer 2

, . , . , :

SELECT friend_id FROM friends WHERE user_id IN (

       SELECT friend_id FROM friends WHERE user_id='$USER_ID'

);

EDIT: , , , . Sry.

Answer 3

"Structure 2", , 100 ( , 10K ?), , 1 :

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

EDIT:

# 1241 , * , colums ( ), "*" "user_id" ( )

1 , , recomand , , ( , ).

2, , , , , HD, mysql. ​​? friend_id, user_id, friend_id?

Answer 4

, 1 2. 1 .

, , - .

"", "", , . , , Graph.

DB "" , , .

, :

http://www.slideshare.net/maxdemarzi/graph-database-use-cases

Neo4j OrientDB .