checking if two users are friends - query advice
I would just like some advice on designing a mysql query to check if two users are friends.
Ive got a table 'friends' with columns 'user1' and 'user2'. The difficultly is that a friend connection is represented by one row so if i want to find whether users 45 and 54 are friends i need to search the table for
user1 = 45 AND user2 = 54 OR user1 = 54 AND user2 = 45.
This is fine, but i need to integrate this as a JOIN so at the moment ive got something like:
(a simplified example of what i want to do)
"SELECT photos.url, photos.caption FROM photos
JOIN friends ON (friend1 = '$userid' && friend2 = photos.userid) || (friend1 = photos.userid && friend2 = '$userid')
So i only want to get photos of users who i am friends with
The above query works, but im my Mysql query stats, this counts as a JOIN query that doesnt use indexs and so the select_full_join variable goes up, and as far as i understand i need to avoid this completely.
What is the best way to go about this? Im sure this isnt an uncommon thing to do or am i going about it completely the wrong way?