best way to do this?

jasondavis

I have a social network site that is growing pretty fast I have a screenshot below of how the system stores whos friend is who's, currently there
are 2,409 accounts and this creates 47,816 rows in the table below, is there a
different way to do this or is this a good way?

this is from 2 days of data being saved so you can just imagine over a few months it will be checking the DB hrough millions of records to see who is who's friend

rulian

you can cut that in half i you rescheme the table
since for every entry you have to enter 2 rows
do what I did, although I have yet to test it

ROW ID | USER 1 ID | STATUS | USER 2 ID | STATUS
You can still expect a lot of rows, but it should at least help streamline it
as was explained to me, this is a classic example of many to many relationship
If you optimize the table and your queries enough it shouldnt pose a problem

jasondavis

here is the php code

here is the php code to do it

function findfriend(){
$temp=$_SESSION['info'];
$sql_friend="select * from friend_friend where userid='$temp[auto_id]' and status='Approve'";
$result_friend=executeQuery($sql_friend);
while($line_friend=mysql_fetch_array($result_friend)){ 
$friend[]=$line_friend['friendid'];
}
$cnt=count($friend);
if($cnt>0)
{ 
$str_friends_ids=implode(",",$friend);
} 
else
{ 
$str_friends_ids='';
}
return $str_friends_ids;
}

is there a better efficient way to do this?

rulian

You have the right idea, as whether that's good for you to work with or not is simply a matter of testing.

If you want to improve your speed a little bit, i'd make the "approve" field a integer and have 0, 1 or numeric values instead so mysql doest have to read as much information.

Return only friend_id's from the query to save yourself some space as well.

Other then that it's pretty much how you use it on your site that determines the rest of the performance. If your using the friend_id to gather information on their friends then you want to optimize that query or join with this one to limit the number of queries you do into the database.