Hi, I have 4-5 different types of models which can be tagged. So, in the taggings table should I declare a multicolumn index on [taggable_type, taggable_id] or a single column index on [taggable_id]. What are the pros and cons of either, and which one is preferred? If multicolumn, then in what order, i.e., [taggable_type, taggable_id] or should it be [taggable_id, taggable_type]? I have another table for tracking views for different objects, and this time there are 3 columns [viewable_type, viewable_id, user_id], so should this one be a 3 column index? My question is not really Rails related, but I am hoping that I will get some some advice from other fellow Web developers. Thanks a lot. -- Surendra Singhi http://ssinghi.kreeti.com, http://www.kreeti.com Read my blog at: http://cuttingtheredtape.blogspot.com/ ,---- | Great wits are sure to madness near allied, | And thin partitions do their bounds divide. | | (John Dryden, Absalom and Achitophel, 1681) `---- --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On Sep 20, 2006, at 7:02 AM, Surendra Singhi wrote:> > Hi, > I have 4-5 different types of models which can be tagged. So, in > the taggings > table should I declare a multicolumn index on [taggable_type, > taggable_id] or > a single column index on [taggable_id]. What are the pros and cons > of either, > and which one is preferred? > > If multicolumn, then in what order, i.e., [taggable_type, > taggable_id] or > should it be [taggable_id, taggable_type]?The database will be able to use an index on [taggable_type, taggable_id] to get all rows for a given type which is almost certainly more likely that wanting all the types sharing an id.> > I have another table for tracking views for different objects, and > this time > there are 3 columns [viewable_type, viewable_id, user_id], so > should this one > be a 3 column index?Generally, every "prefix" of the columns in an index acts almost like an index of those columns, so there''s very little benefit to having both [alpha_id,beta_id] and [alpha_id,beta_id,gamma_id] indices. However, I''ll add both [alpha_id,beta_id] and [beta_id,alpha_id] indices to a join table between "alphas" and "betas" if the lookups come from both directions.> > My question is not really Rails related, but I am hoping that I > will get some > some advice from other fellow Web developers. > > Thanks a lot. > -- > Surendra Singhi > http://ssinghi.kreeti.com, http://www.kreeti.com > Read my blog at: http://cuttingtheredtape.blogspot.com/ > ,---- > | Great wits are sure to madness near allied, > | And thin partitions do their bounds divide. > | > | (John Dryden, Absalom and Achitophel, 1681)you''re welcome, Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org
I thought one chooses indexes with respect to the queries that will be run. With mysql you use EXPLAIN with the SQL statement you''re trying to optimize for. EXPLAIN select a,b,c,d from T where ...... Then you look at the output and see what is missing, or what is overkill. Stephan -- 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 -~----------~----~----~----~------~----~------~--~---