On 2 Oct 2015, at 4:41pm, Bart Smissaert <bart.smissaert at gmail.com> wrote:
> Say we have a table: > > CREATE TABLE TABLE1 > ([FIELD1] INTEGER, > [FIELD2] TEXT, > [FIELD3] TEXT, > [FIELD4] REAL) > > and we have a unique index on all 4 fields: > > CREATE UNIQUE INDEX IDX1 > ON TABLE1 > (FIELD1, FIELD2, FIELD3, FIELD4) This would be rather unusual. It implies that the combination of all the fields in the table has to be unique, but that it's okay to have two rows with the same values for FIELD1, FIELD2 and FIELD3. As you (I think it was you, may have been someone else) mentioned upthread, the extreme rarity of this means that it's not worth checking for it in every use of COUNT(*). The only common case I can think of where there's a UNIQUE requirement for all the columns in a table is where all the table is really just a primary key in itself. And in that situation the software would just scan the primary key index anyway, which would probably be just a rowid, and that is already the optimal case. Simon.
