Rails3 'join' query vs 'in' query?

Lets say I have models    Accounts(:id,:name,:type) has_many :items      Items(:id,:account_id;name) belong_to :account

I want all the items where the account.type = "Cost"

I could do:   income = Item.joins(:account).where('account.type' => "Income")

Or, I could do:

  income = Item.where(:account_id => Account.where(:type => "Income").map(&:id))

While there are several others queries where this is used, the joins approach takes about 20ms in activerecord. The in approach (the inner query produces an array of ids) takes about 10ms.

The Items table is expected to be large, the Accounts table will be small.

Numbers tell me that my array approach is better, but then I have not seen many use that approach.

Any other suggestions? Comments?

Steve

AppleII717 wrote in post #959561:

I could do:   income = Item.joins(:account).where('account.type' => "Income")

Or, I could do:

  income = Item.where(:account_id => Account.where(:type => "Income").map(&:id))

While there are several others queries where this is used, the joins approach takes about 20ms in activerecord. The in approach (the inner query produces an array of ids) takes about 10ms.

Numbers tell me that my array approach is better, but then I have not seen many use that approach.

I don't know how extensive (i.e. how many scenarios) your benchmarking involved, but if it was only one contrived test that may not be enough. For example what effect does an increasing number of values in the in() have on performance?

Searching a single table on an indexed simple integer is obviously going to be faster than a join. However, as the number of values in the "in" clause increases you need to know how that affects performance. For example in(1,2) might be significantly different than in(1,3,5,7,20,50,100,200).

My point is that performance of an in() likely doesn't increase linearly with the number of values.

Thanks for the reply - I don't seem to get many. Your point is well taken and I guess it depends the application. I've done a few in() joins in another application where the array was over a thousand ids and it blasted through it.

I also didn't have the "type" indexed and that would make some difference. I'm just thankful I figured out how to do the join query:-) - have not did many of those.

Steve