Gallery and Ratings

Hello,

i'm currently working on my own gallery script and i previously had the ratings stores within the gallery table and i just counted the number of votes and the total sum of the ratings. But now i want to extend this a little bit and save each vote as i want to be able to determine how a user rated a gallery.
Now i have two tables, one for the galleries and another one to store the ratings. Now my problem is i want to sort the gallery by ratings but the query is very slow. I use the statement bellow:

SELECT gid, name, views, SUM(rating.rating) / COUNT(rating.rating) rating FROM gallery g LEFT JOIN ratings ON ratings.galleryID = g.gid ORDER by rating

Code (markup):

But i have over 500 galleries and a lot of votes. So the query takes alot of time to execute and i wondered if there is a way to optimize my query? Or if it's even the right way to achive what i want


best regards,

Nyu

 

Hi,

Try old school sub-queries:

SELECT gid, 
name, 
views,
(SELECT IFNULL(AVG(rating),0) FROM ratings 
WHERE ratings.galleryID = gallery.gid
) Rating 
FROM gallery 
ORDER by Rating DESC;

Code (markup):

Regards