My English is pretty bad and I think I've explained badly. Simplifying the table is:

create table PRICES {
START_DATE date default current_date not null,
ITEM varchar(10) default '' not null,
PRICE double precision default 0 not null
};
alter table PRICES add constraint PK_PRICES primary key (START_DATE, ITEM);

START_DATE and ITEM fields are not updatable. In the before update trigger the old values ​​are copied to the new values​​. I understand that my question about updates, it was not appropriate in this case.

The data are usually accessed ordering by START_DATE and ITEM, but now in a procedure I use:

for select 1 PRICE from PRICES where ITEM = :ITEM and START_DATE <= :START_DATE into :PRICE do ...

Without going into the issue of "natural primary keys vs subrogate", to optimize this query I created the index:

create unique descending index IDX_PRICE_ITEM on PRICES (ITEM, START_DATE);

I have little knowledge of internal Firebird.

Does using "unique" I'm adding some checking to do that insertions are slower?

In other databases I know there is a difference between unique and non-unique indexes, using lists to keep duplicate values​​. In these cases unique indexes are simpler and faster.

There may be a syntax error (I wrote the sentences by hand), but I hope this time you can understand my question.

Thank you.
Ruben Marti.

_____

De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] En nombre de Helen Borrie
Enviado el: jueves, 14 de febrero de 2013 6:40
Para: firebird-support@yahoogroups.com; firebird-support@yahoogroups.com
Asunto: Re: [firebird-support] Doubt creating index




At 05:48 p.m. 14/02/2013, W O wrote:

>So, Helen, if an index use the column A, then (ideally) the column A should
>not be updatable?
>
>That's new for me.

It is also a wrong take on what I said. "Column A" in this case is a field in the Primary Key. It is extremely poor design to have PK's that have updateable fields.

Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.ibphoenix.com/products/books/firebird_book
__________________________________________________________






[Non-text portions of this message have been removed]