Re: an index question

dcruncher4@aim.com wrote: > I am using IDS 7.31 > > Consider this table > > (col1 integer, > col2 datetime, > col3 integer, > ... other columns > ) > pky = col1 + col2 + col3 > > The problem is that the design of this table is bit odd. Everytime a change > is made to a row of this table, a new row is created with a new timestamp > (col2 in this case) and rest of the columns remain same, except the one > which changed. > > Obviously active row is considered as something with the greatest col2 > for a combo of col1+col3. An appropriate view was declared to show > only the most active row > > create view aaa as > select col1,col3,col4 ...., (select max(col2) from table t2 where t2.col1 => t1.col1 and t2.col3 = t1.col3) from table t1 > > The performance of this query wasn't good on large set of data. > I then decided to try this > > create index aaaa on table(col1 asc, col2 desc, col3 asc)> > The performance gain on the view was incredible. > What can be the reason behind it. Can anyone explain > the internals behind it. > > The only problem I see is that with this index I can not > create a PKY or a unique constraint, and for that I am > forced to create another index. > You'd probably also get even better performance improvement from modifying the index/primary key as: CREATE UNIQUE INDEX mytable_pk ON mytable( col1, col3, col2 desc ); ALTER TABLE mytable ADD CONSTRAINT PRIMARY KEY (col1, col3, col2 ); the order of the columns in the index are not relevant to the quality of the primary key, so... Art S. Kagel > TIA. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >