Re: Unique index or not

C'Mon, Vince, how about sharing your REAL fealings with us now? ;-) Seriously, you're right. It depends. I will challange though your assertion that the unique isn't required for the composite key. Redundant, yes, but there are reasons, mainly for performance considerations, for duplicating the row in another index. If, for example, you frequently referenced the primary key AND the second column in queries, it might be worth the added storage expense to keep an index such as this. Maybe. YMMV, and all that stuff. David PS - I'm not a mathemetician, but I played one in college for 6 years, so understand about all the "mathematical terms" you mentioned. Vince flamed... }FLAME MODE = ON }I cannot speak specifically about Informix, but in mathematical terms... } }If the set A contains unique data, then the set A+B will also contain }unique data } }Therefore, IMHO, }1. The unique is unnecessary for the composite key }2. The unique constraint adds overhead to an insert operation } }However, perhaps a select statement is faster given that only one }row is known to match the query. } }SO, the final answer is "IT DEPENDS" ;-) } }FLAME MODE = OFF }-- }Vince }pachiano@dayton.bassinc.com } }"Opinions expressed may not be correct--- >But at least they're my own !!! ;-) } }Peter Wang <pwang@socs.uts.edu.au> wrote in article <5ob9gs$b7l@cssun.mathcs.emory.edu>... }> Dear all, }> }> Suppose I have a table with three columns: col1, col2, col3. For data }> integraty constraint, there is a unique index created for col1. For }> query performance, I need to add an composite index on col1, and col2, }> Which type of index you would suggest to add: unique or duplicate? I }> think the uniqe type is unnecessary and has a bigger impact on insert. }> But I'm not sure whether it has advantage over the duplicate type on }> select. }> }> Any comments? }> }> Cheers. }>