Efficient SQL Query

toastyman · 2 Oct 2007 at 17:18

Greetings all,

I'm building a community based website for a client, but they've asked me to create something which i'm not sure on how to go about doing...

Basically, you create friends on this website. These details are stored in two tables...

User
User ID
First Name
Last Name
etc..

Friends
Link ID
User ID 1
User ID 2

Easy enough so far!

The client has asked me to build a feature specifying the following:
When the user clicks on another user's profile, and they're not directly friends, work out if they know any of your other friends, to calculate the chance that you might know them..

So for example...
Rich is friends with Phil, and Phil is friends with Dan. However, Rich is not currently friends with Dan. When Rich goes on Dan's profile, it needs to go through all Rich's friends, to see if they know Dan. The theory is, it should find that Phil is friends with Dan.

Now I can do this using PHP, no problem. My idea was to have a loop, and just go through each one of Rich's friends, running an SQL query to see if they know Dan. However, a query per user... if Rich has 400 friends, that is not exactly an ideal solution.

Is there a way of doing this using 1 SQL query, or another way of doing it efficiently?

Any thoughts would be hugely appreciated!

toastyman · 2 Oct 2007 at 18:57

I haven't actually written the query yet, as i'm still in the 'working it out' stage.

Here's my psuedo query (obviously missing the Inner Join)

SELECT `user_id` FROM `users` WHERE (`friends`.``User ID 1` = 'Phil' AND `friends`.``User ID 2` = 'Dan') OR (`friends`.``User ID 1` = 'Dan' AND `friends`.``User ID 2` = 'Phil')

My prediction is it's going to get a lot more complicated!

Yep, Facebook has a similar thing, but what this client has requested has a much more intuitive front end

I really should have just mentioned the Facebook Mutual Friends thing instead of waffling on with examples etc

toastyman · 2 Oct 2007 at 18:59

marc2003 said:
well you need 2 queries. one to return a list of rich's friends. and another to return a list of dan's friends. store the results in php arrays and do the comparing in php?

Oooh, that's another option! Why didn't I think of that?!

toastyman · 2 Oct 2007 at 21:14

Wow thanks all! I think i'm going to try all the solutions given, run benchmarks and work out which one executes the fastest. My guess is it's going to be the SQL given by Mickey, but it can't hurt to test

Thanks for the tip Beansprout! I'll have to research how to create a compound key in MySQL, and all will be good.

Cheers all!