database indexes

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.

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@AgileConsultingLLC.com