DROP the :include key while generating the count query

Rails
1

AFAIK, :include always results in a LEFT OUTER JOIN, which cannot
impact the count and hence should be dropped from the count query
since it slows down the query.

For one LEFT OUTER JOINed object, on PostgreSQL 8.1, I’m getting a 50%
boost after dropping the LEFT OUTER JOIN in the ‘SELECT COUNT’ query
with the same count.

For example:
Consider the following case:

table - blogs :id, :title, :body, :user_id, :published_on etc…
table - users :id, :username, :password, etc

Say I do:

Blog.find(:all, :conditions => "published_on > ‘2007-01-01’
", :include => :user)

This would result in a query like (simplified):

SELECT blogs., users. from blogs left outer join users on
blogs.user_id = users.id WHERE blogs.published_on > ‘2007-01-01’

And a count query like

SELECT count(DISTINCT blogs.id) AS count_all from blogs left outer
join users on blogs.user_id = users.id WHERE blogs.published_on >
‘2007-01-01’

But since it’s a LEFT OUTER JOIN (:include will always do a left outer
join), The above will always be equal to

SELECT count(DISTINCT blogs.id) from blogs WHERE blogs.published_on >
‘2007-01-01’

Basically, IMO, we can drop :include. Dropping the :joins key would
never work cos they may be inner joins.
Or am I missing something???

2

In the most general case the :include can affect the count, if the
conditions reference the :included tables. There’s also a discussion of
this at http://darwinweb.net/

Fred

3

I think 99% of users use the :include to speed-up queries for eager
loading. Eager loading is suggested as an optimization technique.
Ironically, this desired effect is completely lost in the current
implementation.
Since we believe so strongly in conventions, why then can we not
mandate that :include tables cannot be referenced in the :conditions.
If one wants to do so, they ought to use the :joins key.

On Sep 25, 1:39 am, Frederick C. <rails-mailing-l…@andreas-

4

Can you do both :include => :foo and :join foo at the same time ? Can’t
say i’ve tried (for some reason I thought include clobbered ;joins with
it’s own stuff). There has been some discussion on this recently on
rails-core. A big downer would of course be breaking backwards
compatibility. What i tend to use when this bites me is foo_ids =
Foo.find(:all, :conditions => […]).map(&:id)
Foo.find(foo_ids, :include => ‘…’)

Fred