:nikhil_gupte
2007-Sep-24 19:44 UTC
DROP the :include key while generating the count query
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??? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2007-Sep-24 20:39 UTC
Re: DROP the :include key while generating the count query
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
:nikhil_gupte
2007-Sep-24 23:09 UTC
Re: DROP the :include key while generating the count query
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 Cheung <rails-mailing-l...@andreas- s.net> wrote:> 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 athttp://darwinweb.net/ > > Fred > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2007-Sep-25 07:38 UTC
Re: DROP the :include key while generating the count query
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---