Jump to content

Combine Queries


pocobueno1388
 Share

Recommended Posts

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

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.