Thread: 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. 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. Sean
> 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