Re: Indexes on highly duplicate columns - Performance?

Kerry, Your bosses are right. You shouldn't generally put an index on a field with large numbers of duplicate values. But the limit is of the order of 32000 repeats with an index size of char(2). So it wouldn't really matter. But it would do if the table size grew. I would recommend this as a potential subject to be written up for the FAQ. I certainly would be prepared to write it if I had the time. Malcolm Wallans Online Database Consultancy > We have a little column (CHAR(2)) in a table of about 800 rows. > There are about 6 different values in the column. > > People here (who pay me money) reckon I should create an index on it > that looks like this: > > So: CREATE INDEX myindex ON mytable (little_column, > other_uniqueish_column) > > Not: CREATE INDEX myindex ON mytable (little_column) > > Their logic states that one should add some other columns into the > index so that the B-Tree isn't filled with lots of duplicate records. > > Is this sensible? Anybody else heard this theory before? JL? > > Regards, > Kerry S > --------------------------------------,--------------------------------- > ----Kerry Sainsbury, kerry@kcbbs.gen.nz | THE INFORMIX FAQ > Quanta Systems, Auckland, New Zealand | kcbbs.gen.nz:/informix/* > | > mathcs.emory.edu:/pub/informix/faq/*+64 9 377-4473 (work) 276-5546 > (home) | quasar.ucar.edu:/?