Re: Index choice

> I have a fairly basic question: what is the best
> choice of indices for a
> table? In practice, I have a table with 20+ million
> rows and about 25
> columns.

size matters! -lol- i'm impressed.

> Some are very fine-grained (integers on
> the order of 10^8 for a
> range) and some are much less (6 values over the 20M
> rows). Are there
> general rules for what and in what combinations to
> index (besides index
> columns that are going to be looked up often)? Any
> good links?
>
> Thanks and sorry for the vague, ill-formed
> questions.

this question caught my interest so i checked wrox's
book "beginning databases with postgresql" and came
up with the following:

good:

1. indexes decrease query time.

bad:

1. indexes increase the time required for inserts,
updates and deletes.
2. indexes increase the size of the db.

balance the good and the bad and when the good
ourweighs the bad, an index is likely a good idea.

the rules of thumb seem to be:

1. there are no hard and fast rules.
2. tables with lots of rows that aren't updated too
frequently.
3. columns used in complex joins (i think both primary
and foreign keys are already indexed).
4. columns searched for exact or prefix matches
5. you may have to test an index or series of indices
to see if it actually is worthwhile to implement.

http://www.peachpit.com/articles/article.asp?p=31206&seqNum=4&rl=1

hopefully this was helpful.

____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs