--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> What happens if you try:
>
> CREATE TABLE TEST_TAB (
> ID INTEGER NOT NULL PRIMARY KEY,
> NUMER INTEGER,
> NUMER_NEG INTEGER,
> FOREIGN_KEY INTEGER);
>
> SET TERM ^^ ;
> CREATE TRIGGER TRG_NUMER_NEG FOR TEST_TAB ACTIVE BEFORE INSERT POSITION 0 AS
> begin
> new.NUMER_NEG = new.-NUMER
> end^^
> SET TERM ; ^^
>
> CREATE INDEX TEST_TAB_IDX1 ON TEST_TAB (FOREIGN_KEY, NUMER_NEG);
>
> Is the query
>
> select min(t.numer_neg)
> from test_tab t
> where t.foreign_key = :some_value
>
> equally slow as your MAX-query? Which Firebird version are you using?
>

I've tried without trigger, just added new field and populated it with negative values.
It was much faster! It even didn't use this compound index (only FK index was used).

But the fastest for a now is this query:

select first 1 numer
from test_tab t
where t.foreign_key = :some_value
order by t.foreign_key desc, t.numer desc

It REALLY use compound index (foreign_key, numer).

Firebird 2.1

Regards, Tomek