Re: Unique index or not

I'm not sure that Vince's answer was as incendiary as he thinks... >Peter Wang <pwang@socs.uts.edu.au> wrote: >> 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. And Vince Pachiano <pachiano@dayton.bassinc.com> responded: >X-Informix-List-Id: <news.39409> > >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 The composite key will also be unique. I think that you should therefore tell the system that it is unique. >2. The unique constraint adds overhead to an insert operation Has anybody else ever quantified this? I would expect the difference to be minimal. Since I'd not previously done the measurements, I created a table: CREATE TABLE x(i INTEGER NOT NULL, s CHAR(10) NOT NULL); I created it with a unique index on both columns, and populated it with 1000 rows (each insert separately prepared), and over 5 separate runs, it took 11.224763, 13.228875, 12.405178, 11.358340, 13.790788 seconds. I created it with a dups index on both columns and over 5 separate runs, it took 11.675191, 12.309695, 11.246575, 12.283506, 11.463903. These runs were interleaved. The mean and standard deviations for these data sets are: # Unique index # Count = 5 # Mean = 12.40 # Std Dev = 1.12 # Duplicate index # Count = 5 # Mean = 11.80 # Std Dev = 0.48 # Combined times # Count = 10 # Mean = 12.10 # Std Dev = 0.88 I've not done a t-test on this, but I doubt if the difference is statistically significant. More tests, with a greater diversity of index structures and numbers of rows, might indicate that it is a significant difference, but the difference appears to be of the order of 5%. >However, perhaps a select statement is faster given that only one >row is known to match the query. I leave it to someone else to quantify the performance impact of the different indexes on SELECT statements. >SO, the final answer is "IT DEPENDS" ;-) > >FLAME MODE = OFF I'm still not clear what was inflammatory about Vince's comments... Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>