MySQL Forums
Forum List  »  Newbie

Re: Help with a Left Join
Posted by: Brandon Rodriguez
Date: August 15, 2004 11:54AM

Thanks for the tips. I changed the query accordingly and ran it a few more times. It was still taking a long time to execute so I used this SQL execution time script (http://www.webclass.ru/tut.php?tut=218) and page a page where it only does performs the query and prints it out. The results:

SQL queries: 1, mysql time: 48.946475, total: 48.947693 seconds! SQL percents: 100%

48 seconds for one mySQL query! As for the explain command, I did it on the query and it returned:

table type possible_keys key key_len ref rows Extra
games_files ALL NULL NULL NULL NULL 1673 Using temporary; Using filesort
games_ratings ALL NULL NULL NULL NULL 7922

Which probably won't format very well since this isn't monospaced but nevertheless...

Either way, here's the query that was tested:
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 AND games_ratings.rating!=0 AND games_files.type=4 AND games_files.contestid=''
GROUP BY games_files.id
ORDER BY games_files.id DESC LIMIT 10

Options: ReplyQuote


Subject
Written By
Posted
August 15, 2004 12:25AM
August 15, 2004 03:01AM
Re: Help with a Left Join
August 15, 2004 11:54AM
August 15, 2004 02:50PM
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.