php - Best way to scale data, decrease loading time, make my webhost happy -

for facebook application, have store list of friends of user in mysql database. list requested db, compared other data, etc.

currently, store list of friends within user table, uids of friends put in 1 'text' field, '|' separator. example:

id - uid - name - friends => 1 - 123456789 - john doe - 987654321|123456|765432

my php file requests row , extracts list of friends exploding field ('|'). works fine, every 1000 users 5mb diskspace.

now problem:

for feature, need save names of friends of user. can in different ways:

1) save data in table. example:

id - uid - name => 1 - 1234321 - jane doe

if need name of friend id 1234321, can request name table. however, problem table keep growing, until users on facebook indexed (>500million rows). webhost not going this! such table take 25gb of diskspace.

2) solution extend data saved in user table, adding name uid in friends field (with separator, let's use ','). example:

id - uid - name - friends => 1 - 123456789 - john doe - 987654321,mike jones|123456,tom bright|765432,rick smith

for solution have alter script, add explode (','), etc. i'm not sure how many diskspace going take... data doesn't easy handle way!

3) third solution gives overview of data, cause database huge. in solution create table of friends, row every friendship. example:

id - uid - frienduid => 1 - 123456789 - 54321

id - uid - frienduid => 3 - 123456789 - 65432

id - uid - frienduid => 2 - 987654321 - 54321

id - uid - frienduid => 4 - 987654321 - 65432

as can see in example, gives overview of friendships. however, 500million users, , let's average of 300 friendships per user, create table 150billion rows. host not going that... , think kind of table take lot of diskspace...

so... how solve problem? think, best way store uids + names of friends of user on facebook? how scale kind of data? or have (better) solution 3 possibilities mentioned above?

hope can me!

i agree amber, solution 1 going efficient way store data. if want stick current approach (similar solution 2), may want consider storing friendship data json string. won't produce shortest possible string, easy parse.

to save data:

$friends = array(     'uid1' => 'john smith',     'uid2' => 'jane doe' );  $str = json_encode($friends);  // save $str database in "friends" column 

to data back:

// $str database  $friends = json_decode($str, true);  var_dump($friends);