Optimize Query

[The Little Guy]

I have this query:

SELECT * 
FROM (
     SELECT b.id, b.name, COUNT( * ) AS ratings,
     (
          (
               SUM( managers + difficulty + fun + hours + pay + flexibility ) / 6
          ) / COUNT( * )
     ) AS total
     FROM ratings r
     LEFT JOIN businesses b ON ( r.business_id = b.id ) 
     WHERE r.status =2
     GROUP BY b.name
     ORDER BY total DESC 
     LIMIT 10
) AS tmp
ORDER BY tmp.total

 

when my table (hopfully) gets to 2 million plus rows, I am not sure if this query will run quickly or not, any suggestions on how I can optimize it?

 

Right now I have an indexe on "ratings.status"

 

basically it gets the top 10 rated businesses in my database, it runs really fast right now, but I only have about 10 rows. So any suggestions?

[kickstart]

Hi

 

The outer select seems redundant, but I presume when you really use it you will join the results of the sub select with another tables data.

 

You appear to be manually calculating the average rather than using the AVG function.

 

You probably want an index on ratings.business_id

 

Hopefully someone can help with something more useful than these points though.

 

All the best

 

Keith

[The Little Guy]

would avg be faster than:

SUM( managers + difficulty + fun + hours + pay + flexibility ) / 6

???

 

I added a key to ratings.business_id and it didn't use that key, so I removed it

 

The outer join was just to reverse the order of my 10 results so I could have a top ten count down instead of count up.  But then we decided to have it count up so I removed the outer select.

[kickstart]

Hi

 

Not sure it is noticeably faster to use AVG, but it is a bit more readable.

 

SELECT b.id, b.name, COUNT( * ) AS ratings, avg(( managers + difficulty + fun + hours + pay + flexibility ) / 6) AS total
     FROM ratings r
     LEFT JOIN businesses b ON ( r.business_id = b.id ) 
     WHERE r.status = 2
     GROUP BY b.id, b.name
     ORDER BY total DESC 
     LIMIT 10

 

All the best

 

Keith