Optimizing Sql - fewer queries

pederdin

I have a community where people can invite others. I have a table with two columns: owner_id, friend_id

Owner_id is the customer inviting and friend_id is the person I invite. Now I want to see a tree of all invitors and all that are invited. My solution is this:

<?
$loop=1;
$tree=1;
$customer_id[$tree]=1;
$limit[1]=0;
?>

<table width=1500 cellpadding=0 cellspacing=0>
<?
while ($all_customers<991){
$sql = mysql_query("SELECT p.owner_id,
                                              p.friend_id
                                      FROM
                                      private_friends_pending p  

                                      WHERE
                         p.owner_id='".$customer_id[$tree]."'                     

                                     ORDER BY p.id limit ".$limit[$loop].",1");
$row=mysql_fetch_array($sql);
if($row['friend_id']){

$width=$tree*20;

echo "<tr><td class=xxbla bgcolor=ffffff><table cellpadding=0 cellspacing=0><tr><td width=$width class=xxbla  align=right></td><td class=xxbla bgcolor=ffffff>".$row['friend_id']." </td></tr></table></td></tr>\n";

$tree++;
$loop++;
$limit[$loop]=0;

$customer_id[$tree]=$row['custid'];

$all_customers++; 

} else {

$tree--;
$loop--;
$limit[$loop]++;

}



}
?>

</table>

It goes through the table and looks for owner and gets all friends. If it finds a friend it checks if that customer has any friends. And when it doesn't find any friends it goes back one level until the whole table is worked through.

This gives me the result I want, but now I have 900 members so I make 900 queries and I'm wondering if there is a way of getting the same tree-result in one query.

Sxooter

Ouch, that's a toughy in MySQL. In Oracle or some other databases, there is a connectby type statement that will let you build recursive queries. With MySQL it might be best for you to use a large number of ids in a in() clause or something like that. how many levels deep does this thing need to go?

pederdin

I'm downt to ten levels now so my old version with a new query for every level actually works but I have to change the code for every new level that comes up.

That version, though, needs much fewer queries as I only make one query for every invitor. Now I make one query for every connection which gives me nicer code but much worse optimization.

Is there any other way?

Sxooter

Maybe a self join of several levels will work, something like:

select * from
table a, table b left join on (a.id=b.parent_id),
table c left join on (c.id=b.parent_id),
table d left join on (d.id=c.parent_id),
(repeat as necessary)...

Would that work? It might be kinda slow as a single query, but assuming good indexing, it should still be much faster than running the same query over and over with a different where clause.