Combine Queries

[pocobueno1388]

Hello everyone,

 

I have a script that will display users according to the distance they are from the logged in user. It works great, it is just extremely slow to load, which I'm assuming it's from all the queries I'm doing. I'm pretty sure I should be able to combine most of them and just have one, I'm just not advanced enough to figure it out.

 

To start there is a query to display the users picture. Then I do a query to display all the people the user has "favorited". Then I select the users who they have unread messages from. After that I then do a query to grab the rest of the users and display them in order of distance from the logged in user.

 

So first I display the logged in user

SELECT 
   u.name, u.id, u.last_login, p.path
FROM 
   users u
LEFT JOIN 
   photos p
ON 
   p.userID = u.id AND p.type=1
WHERE 
   u.id = '$user->userID'

 

Then I display their favorites

SELECT favorite_userID FROM favorites WHERE userID='$user->userID'

SELECT 
   u.name, u.id, u.last_login, p.path
FROM 
   users u
LEFT JOIN 
   photos p
ON
   p.userID = u.id AND p.type=1
WHERE 
   u.id IN ($favorites) AND u.id NOT IN ($all_blocked)

 

Now I select the people they have unread messages from, but before that I have to do a query to get all the blocked users

SELECT block_userID FROM block WHERE userID='$user->userID'

SELECT 
   DISTANCE QUERY as distance, u.name, u.id, u.last_login, p.path
FROM 
   users u
LEFT JOIN
   photos p
ON
   p.userID = u.id AND p.type=1
HAVING 
   distance <= ".$distance." AND u.id != '$user->userID' AND u.id NOT IN ($favorites) AND u.id NOT IN ($all_blocked)
ORDER BY 
   distance

 

Lastly I select the rest of the users and display them by distance from the person

SELECT
  DISTANCE QUERY as distance, u.name, u.id, u.last_login, u.sessionID, p.path,
  DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(u.birthday)), '%Y')+0 AS age
FROM 
    users u
LEFT JOIN
    photos p
ON
    p.userID = u.id AND p.type=1
HAVING 
    distance <= ".$distance." AND u.id != '$user->userID' AND u.id NOT IN ($favorites) AND u.id NOT IN ($all_blocked)
    AND u.sessionID='1' AND age >= $min_age AND age <= $max_age

 

So that is already 6 queries which are all pretty large.

 

If anyone can help me combine these, I would GREATLY appreciate it! Thanks

[pocobueno1388]

Please ask questions if you think something is to vague. Thanks

[pocobueno1388]

Anyone?

[Illusion]

If I am not wrong ...I don't think you can combine any of these queries as none of queries output is subset of any other output.

 

If queries are longer time find a way to optimize them.

[fenway]

I don't know what a "large query" is, and why it's a problem.