At 07:57 PM 2/03/2010, you wrote:

>the query execution time now is less than 1/4 of previous :)
>
>i understand why use an index also in source_id (there're about 200 different source ids), my question was why i need to order also by source-id desc but i think that i must read more on how multiple index works to udnerstand it.

You don't order by source-id if you want the highest date!!

SELECT FIRST 1 r.ID from RESULTS r
WHERE r.SOURCE_ID = :SourceId
ORDER BY r.DATE DESC

This query needs the DESC index on r.DATE because SELECT FIRST operates on an ordered output set.

Using the max function, correcting your original query

SELECT r.ID FROM RESULTS r
WHERE r.SOURCE_ID = :SourceId
AND r.DATE = (SELECT MAX(r2.DATE) FROM RESULTS r2
WHERE r2.id = r.id)

This one needs the DESC index on the date field, too.

Another one, simpler, might go faster, if you don't mind getting the date value in your output along with the ID:

SELECT id, max(date)
from results
where id = :source_id
group by by 1

./heLen