Subject Re: query optimization- MAX() from .. WHERE
Author tomjanczkadao
> >It doubles reads amount.

Sorry, i meant there is one read, but it's very slow.

> That seems to tell us that FOREIGN_KEY and NUMER both increase systematically as the database that the case?


> If so, test the benefit of placing a descending index on both (but not a compound descending index involving both).
> ./heLen

Actually it does not matter whether foreign_key has ascending or descending index. There's one read, but very slow, about 25ms (this query is executed many times in loop).

But I've temporary solution for this case:

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). Query is about 1000x (!) faster. tested in loop.

Regards, Tomek