# Find\_by\_sql preformance problems when ordering data

**URL:** <https://www.ruby-forum.com/t/find-by-sql-preformance-problems-when-ordering-data/121728>\
**Category:** Rails\
**Created:** [November 30, 2007, 10:00am UTC](https://www.ruby-forum.com/t/find-by-sql-preformance-problems-when-ordering-data/121728 "2007-11-30T10:00:04Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![mgiorno](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e274bd/32.png) [@mgiorno](https://www.ruby-forum.com/u/mgiorno)\
**Post date:** [November 30, 2007, 10:00am UTC](https://www.ruby-forum.com/t/find-by-sql-preformance-problems-when-ordering-data/121728/1 "2007-11-30T10:00:04Z")

</div>

Hi,

I am bumping into a small problem I was hoping someone could send me in  
the right direction on.

I’m counting votes from a votes table to try to find the top ten videos.  
The following works really well, until I try to order it. It would seem  
that the order by is adding a 26 second delay, which surprised me since  
it was all done in sql. I revised the process to sort and publish on  
the top 10 items in rails, which has gotten the delay down to about 5-8  
seconds.

Video.find\_by\_sql(“select videos._, (select count(_) from votes where  
video\_id = videos.id and value = 1) - (select count(\*) from votes where  
video\_id = videos.id and value = -1) AS total\_votes from videos ORDER BY  
total\_votes DESC LIMIT 10”)

Is there some better way to order these results that wouldn’t be more  
resource intensive. I was looking around to see if maybe my total\_votes  
alias could be clearly defined as an integer, which I though might help  
speed up the order by. I haven’t been able to find anything thus far.

Any help would be appreciated!

---

<div class="post-metadata">

**Author:** ![mgiorno](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e274bd/32.png) [@mgiorno](https://www.ruby-forum.com/u/mgiorno)\
**Post date:** [November 30, 2007, 10:47am UTC](https://www.ruby-forum.com/t/find-by-sql-preformance-problems-when-ordering-data/121728/2 "2007-11-30T10:47:17Z")

</div>

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

On Nov 30, 9:00 am, Mario F. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![mgiorno](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e274bd/32.png) [@mgiorno](https://www.ruby-forum.com/u/mgiorno)\
**Post date:** [November 30, 2007, 1:19pm UTC](https://www.ruby-forum.com/t/find-by-sql-preformance-problems-when-ordering-data/121728/3 "2007-11-30T13:19:03Z")

</div>

> (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.