# DROP the :include key while generating the count query **URL:** \ **Category:** Rails\ **Created:** [September 24, 2007, 9:45pm UTC](https://www.ruby-forum.com/t/drop-the-include-key-while-generating-the-count-query/115398 "2007-09-24T21:45:25Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![SSnikhil\_gupte](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/3ab097/32.png) [@SSnikhil\_gupte](https://www.ruby-forum.com/u/SSnikhil_gupte)\ **Post date:** [September 24, 2007, 9:45pm UTC](https://www.ruby-forum.com/t/drop-the-include-key-while-generating-the-count-query/115398/1 "2007-09-24T21:45:25Z")
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??? ---
**Author:** ![SSnikhil\_gupte](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/3ab097/32.png) [@SSnikhil\_gupte](https://www.ruby-forum.com/u/SSnikhil_gupte)\ **Post date:** [September 24, 2007, 10:39pm UTC](https://www.ruby-forum.com/t/drop-the-include-key-while-generating-the-count-query/115398/2 "2007-09-24T22:39:30Z")
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/](http://darwinweb.net/) Fred ---
**Author:** ![SSnikhil\_gupte](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/3ab097/32.png) [@SSnikhil\_gupte](https://www.ruby-forum.com/u/SSnikhil_gupte)\ **Post date:** [September 25, 2007, 1:10am UTC](https://www.ruby-forum.com/t/drop-the-include-key-while-generating-the-count-query/115398/3 "2007-09-25T01:10:45Z")
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. \ **Author:** ![SSnikhil\_gupte](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/3ab097/32.png) [@SSnikhil\_gupte](https://www.ruby-forum.com/u/SSnikhil_gupte)\ **Post date:** [September 25, 2007, 9:38am UTC](https://www.ruby-forum.com/t/drop-the-include-key-while-generating-the-count-query/115398/4 "2007-09-25T09:38:34Z") 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