The best way to scale data, reduce load time, make my web hosting happy

Question

For the Facebook application, I need to save the user's friends list in my MySQL database. This list is being requested from my db, compared to other data, etc.

I currently store this list of friends in my user table, friends uids are collected in one text field, with '|' as a separator. For instance:

ID - UID - NAME - FRIENDS => 1 - 123456789 - John Doe - 987654321 | 123456 | 765432

My PHP file requests this line and retrieves the friends list, exploding this field ('|'). All this works great, every 1000 users have about 5 MB of disk space.

Now the problem is:

For an additional function, I also need to save the user names of the user. I can do it differently:

1) Save this data in an additional table. For instance:

ID - UID - NAME => 1 - 1234321 - Jane Doe

If I need a friend’s name with ID 1234321, I can request a name from this table. However, the problem is that this table will continue to grow until all users on Facebook are indexed (> 500 million rows). My web host won't like this! Such a table will take about 25 GB of disk space.

2) Another solution is to expand the data stored in the user table by adding the name in the UID to the friends field (with an additional separator, using ","). For instance:

ID - UID - NAME - FRIENDS => 1 - 123456789 - John Doe - 987654321, Mike Jones | 123456, Tom Bright | 765432, Rick Smith

script, (',') .. , ... " t !

3) , . , . :

ID - UID - FRIENDUID = > 1 - 123456789 - 54321

ID - UID - FRIENDUID = > 3 - 123456789 - 65432

ID - UID - FRIENDUID = > 2 - 987654321 - 54321

ID - UID - FRIENDUID = > 4 - 987654321 - 65432

, . , 500 , , , 300 , 150 . ... , ...

... ? , UID + Facebook? ? () , ?

, !

Answer 1

Amber, 1 . ( 2), JSON. , .

:

$friends = array(
    'uid1' => 'John Smith',
    'uid2' => 'Jane Doe'
);

$str = json_encode($friends);

// save $str to the database in the "friends" column

:

// get $str from the database

$friends = json_decode($str, TRUE);

var_dump($friends);

Answer 2

ID 1234321, . , Facebook ( > 500 ). ! 25 .

25 , 25 . , - . , . , Facebook ( , , 25 ).

, Facebook ( ) , , , , , .

- ; .

Answer 3

, . .
, 1 2, 2 1. .
150 . , , !
, 300 , ( 1) 299. , , !
Also, when you want to start searching for specific relationships, the third option will be much faster, because instead of an index, fulltextyou will get an index intthat is likely to save another 50% in storage and processing speed.

If your application reaches 500 million users, you just need to get the best hosting service.