Jump to content

Optimize Query


The Little Guy

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/230775-optimize-query/
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/230775-optimize-query/#findComment-1188093
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/230775-optimize-query/#findComment-1188573
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/230775-optimize-query/#findComment-1188602
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.