0

I basically have two tables, a 'server' table and a 'server_ratings' table. I need to optimize the current query that I have (It works but it takes around 4 seconds). Is there any way I can do this better?

SELECT ROUND(AVG(server_ratings.rating), 0), server.id, server.name
FROM server LEFT JOIN server_ratings ON server.id = server_ratings.server_id
GROUP BY server.id;
Syntax
  • 2,073
  • 15
  • 15

2 Answers2

1

Query looks ok, but make sure you have proper indexes:

  • on id column in server table - probably primary key,
  • on server_id column in server_ratings table,

If it does not help, then add rating column into server table and calculate it on a constant basis (see this answer about Cron jobs). This way you will save the time you spend on calculations. They can be made separately eg. every minute, but probably some less frequent calculations are enough (depending on how dynamic is your data).

Also make sure you query proper table - in the question you have mentioned servers table, but in the code there is reference to server table. Probably a typo :)

Community
  • 1
  • 1
Tadeck
  • 132,510
  • 28
  • 152
  • 198
  • I'll look into Cron jobs, this is probably what I will need to do. I have all the indexes right, and yes it was a typo, fixed it :P – Syntax Dec 08 '11 at 06:09
0

This should be slightly faster, because the aggregate function is executed first, resulting in fewer JOIN operations.

SELECT s.id, s.name, r.avg_rating
FROM   server s
LEFT   JOIN (
    SELECT server_id, ROUND(AVG(rating), 0) AS avg_rating
    FROM   server_ratings
    GROUP  BY server_id
    ) r ON r.server_id = s.id

But the major point are matching indexes. Primary keys are indexed automatically. Make sure you have one on server_ratings.server_id, too.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I couldn't quite figure out how to fix the GROUP BY area, got a syntax error there. And yes I do have indexes there, I just recently learned their usefulness, thankyou. – Syntax Dec 08 '11 at 06:09