# Optimizing find on join table

**URL:** <https://www.ruby-forum.com/t/optimizing-find-on-join-table/145602>\
**Category:** Rails\
**Created:** [August 11, 2008, 11:35pm UTC](https://www.ruby-forum.com/t/optimizing-find-on-join-table/145602 "2008-08-11T23:35:51Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![rsoerensen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/58956e/32.png) [@rsoerensen](https://www.ruby-forum.com/u/rsoerensen)\
**Post date:** [August 11, 2008, 11:35pm UTC](https://www.ruby-forum.com/t/optimizing-find-on-join-table/145602/1 "2008-08-11T23:35:51Z")

</div>

Hi,

I have a application where i want to rank players according to the goals  
they have scored. I can actually make the ranking work, but I have  
performance problems when I try to fetch the total opponent score (goals  
scored again an user in any match).

Basically what I do is call player in @players.find(:all) and apply the  
action oppenent\_total\_score in the players model like this:

class Player \< ActiveRecord::Base

has\_many :participations, :class\_name =\> “MatchParticipant”  
has\_many :matches, :through =\> :participations

def opponent\_total\_score(player)  
@opponent\_score = 0  
matches.each do |score|  
player\_side = score.participants.find(:last, :conditions =\>  
[“player\_id = ?”, player]).side  
opponent\_score = score.participants.find(:first, :limit =\> 10,  
:conditions =\> [“side != ?”, player\_side]).score  
@opponent\_score = @opponent\_score + opponent\_score  
end  
return @opponent\_score

end

end

The problem arises as soon as there are more than 250 matches, where it  
takes about 0,5 seconds to process the query.

With a database schema looking like this:

create\_table “match\_participants”, :force =\> true do |t|  
t.integer “player\_id”, :null =\> false  
t.integer “match\_id”, :null =\> false  
t.string “side”, :null =\> false  
t.integer “score”  
t.boolean “winner”  
end

create\_table “matches”, :force =\> true do |t|  
t.datetime “created\_at”  
t.datetime “updated\_at”  
end

create\_table “players”, :force =\> true do |t|  
t.string “name”  
t.string “login”  
t.string “password”  
t.string “email”  
t.datetime “created\_at”  
t.datetime “updated\_at”  
end

end

Where match\_participant is a join table of players and matches in  
has\_many and belongs\_to relations. One match can have either 2 or 4  
participants.

How can i optimize this query to make it possible to search even more  
matches without performance issues? The problem, as I see it, is that I  
can’t do a clean SQL query, since I have to determine the player’s side  
before I know what score I will have to sum (the opponent’s score).

Will I need to go for a different database structure in order to  
archieve better performance? As this is obviously an very important  
feature of a player ranking system I would like to make it as effective  
as possible.

Thanks,  
Rune

---

<div class="post-metadata">

**Author:** ![rsoerensen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/58956e/32.png) [@rsoerensen](https://www.ruby-forum.com/u/rsoerensen)\
**Post date:** [August 12, 2008, 9:52pm UTC](https://www.ruby-forum.com/t/optimizing-find-on-join-table/145602/2 "2008-08-12T21:52:42Z")

</div>

On 11 Aug 2008, at 22:35, Rune Soerensen wrote:

> Basically what I do is call player in @players.find(:all) and apply  
> matches.each do |score|  
> end
> 
> The problem arises as soon as there are more than 250 matches, where  
> it  
> takes about 0,5 seconds to process the query.

One of the causes of your slowdown is that for each match you’re  
making 2 queries, so for 250 matches you make 500 queries. Just the  
latency required for a single query (say you can do a query in 1ms)  
adds up to 0.5s.

You could do something like

matches\_with\_participants = matches.find(:all, :include  
=\> :participants)  
matches\_with\_participants.each do |match|  
player\_side = match.participants.to\_a.find {|p| p.player\_id =  
player.id}.side  
opponent\_score = match.participants.to\_a.find {|p| p.side !=  
side }.side  
…  
end

Note that the find here is a regular ruby array find, not an active  
record find.

There is some repetition of the data: the score field is repeated on  
all players, if it were more normalized you might have an easier job.  
For example if you had a sides model describing a side for a match, then

class Side  
belongs\_to :match  
has\_many :players, :through =\> :caps  
has\_many :opposing\_sides, :class\_name =\> ‘Side’, :conditions =\>  
‘match\_id = #{match\_id} AND id != #{id}’ #the single quotes here are  
important  
end  
sides also has a score attribute and a winner attribute.  
In addition, Match has\_many :sides.

Then your query could be rewritten as

SELECT SUM(opponents.score) from sides  
inner join caps on caps.player\_id = #{player.id}  
inner join sides as opponents on opponents.match\_id = sides.match\_id  
where opponents.id != sides.id

which you can probably rewrite to use active record stuff if you feel  
like it (untested)  
player.sides.sum(:all, :select =\> ‘opponents.score’, :joins =\> “inner  
join sides as opponents on opponents.match\_id = sides.match\_id”.  
:conditions =\> “opponents.id != sides.id”

which show be pretty nippy as long as you have indexes on the match\_id  
and player\_id  
(This is just off the top of my head to get the ball rolling - I don’t  
understand the data you’re modelling so there may be things that you  
know about that would kill this idea)

Fred