find_by_sql preformance problems when ordering data

rubyonrails-talk
1

Hi,

Apart from suggesting that you check that you have indexes on the obvious places (id, value), I would recommend changing your app to do either

(a) using a select sum(value) on the id field

(b) don't store the votes directly: change the logic of your application to update running totals.

You could make it safe by not using a cache and writing the updates directly to the database - in which case you would only get the performance boost when retrieving the score

or

You could go the whole hog and have a distributed memcache and just periodically update the total

Option (a) is likely to be the correct choice unless you have very large volumes.

HTH

Chris

2

(a) using a select sum(value) on the id field

That was badly worded what I mean is that you can sum all of the values for a particular "videos_id" rather than separately summing the plus votes and the minus votes.

You should also try to avoid that (potentially) big join by getting the top ten votes and then selecting the corresponding records from the videos table.