MySQL Forums
Forum List  »  Newbie

Re: Help with a Left Join
Posted by: Nick Roper
Date: August 15, 2004 03:31PM

Brandom,

1) Create indices on the games_ratings.rating, games_files.type and games_files.contestid columns.

2) Take the row selection criteria out of the LEFT JOIN clause and put them in a separate WHERE clause.

3) If there are no -ve values for games_ratings.rating then change the condition to: games_ratings.rating > 0

4) Are you sure about the games_files.contestid='' ? The table definition shows a default of '0'. Should this be games_files.contestid = '0' ?

So:

SELECT games_files.id, games_files.trackname, games_files.screenshot, games_files.user, games_files.time, AVG(games_ratings.rating) AS average_rating FROM games_files
LEFT JOIN games_ratings ON games_files.id=games_ratings.trackid WHERE games_ratings.rating > 0 AND games_files.type=4 AND games_files.contestid='0'
GROUP BY games_files.id
ORDER BY games_files.id DESC LIMIT 10;

Let me know,

Nick



--
Nick Roper

Options: ReplyQuote


Subject
Written By
Posted
August 15, 2004 12:25AM
August 15, 2004 03:01AM
August 15, 2004 02:50PM
Re: Help with a Left Join
August 15, 2004 03:31PM
August 15, 2004 03:38PM
August 16, 2004 02:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.