Hello,


On Firebird 2.5.3 (SS) 64 Bit


CREATE GENERATOR GEN_test_ID;

CREATE TABLE test (
id INTEGER NOT NULL,
reference INTEGER,
key INTEGER,
value VARCHAR(200)
);


ALTER TABLE test ADD CONSTRAINT PK_test PRIMARY KEY (Id);

SET TERM ^ ;

CREATE OR ALTER TRIGGER test_BIU0 FOR test
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
IF (NEW.Id IS NULL) THEN
NEW.Id = GEN_ID(GEN_test_ID,1);
END
^

SET TERM ; ^


Insert 999 random records
dublicate one record (with new id of course


select min(t.Id) FROM test t
group by t.reference, t.key
having count(*) > 1

costs 1000 Non indexed reads

select * from test where Id in (
select min(t.Id) FROM test t
group by t.reference, t.key
having count(*) > 1
)

costs 1001000 non indexed reads


Why?


Creating an index modifies only non indexed in indexed reads.


Björn