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.

Surendra S.
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)
`----

On Sep 20, 2006, at 7:02 AM, Surendra S. wrote:

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.

,----
| 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 B. http://agileconsultingllc.com
[email protected]

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs