--- In firebird-support@yahoogroups.com, "Sergio H. Gonzalez" wrote:

> Hello, I have a currency's table and a history of how the value of
> each currency changes. I need to select all the currencys (they are
> less than 10) plus the id of the last change in the history. I'm
> using this and it seems to work ok. But with a very populated
> history table, the query slows down.
>
> The first question I have is:
>
> 1) why FB is not using the descending index I've created on
> MONEDAS_VALORES to extract the MAX id ?
>
> 2) is that the better way to do what I'm doing?
>
> Thanks!!
>
>
> --THIS IS THE SELECT--
>
> select
> m.id,
> m.descripcion,
> m.valor,
> max( v.id )
>
> from monedas_valores v
> inner join monedas m on (v.id_monedas = m.id)
> group by
> m.id, m.descripcion, m.valor

Hi Sergio!

Firebird doesn't know that all currencies are likely to have changed lately and thus be early in the index. If the descending index had been on, say, valor, then all occurences of Zimbabwe dollar would have come before the highest value of US dollar in the index and using the index would slow down the query.

You may be able to use a pragmatic approach to your problem. E.g. if all currencies will have records within the last 1000 changes, then you can write

select m.id, m.descripcion, m.valor, max( v.id )
from monedas_valores v
join monedas m on v.id_monedas = m.id
where v.id > gen_id(<id_generator>, 0) - 1000
group by m.id, m.descripcion, m.valor

This should be able to benefit from your descending index. If things changes very rapidly (e.g. you may envision a situation where a currency changes 10000 times in a day), you may consider adding a field DATE_REGISTERED or TIME_REGISTERED and ascertain that each currency gets at least one entry each day (or week or whatever) and add the appropriate WHERE clause similar to above.

Another option is to have another table, populated through a trigger that generally store id_monetas, id every time you store a record, and that every once in a while (maybe each night or even once every hour) run:

DELETE FROM MAX_REGISTERED MrWas
WHERE EXISTS(SELECT * FROM MAX_REGISTERED MrIs
WHERE MrWas.ID_MONETAS = MrIs.ID_MONETAS
AND MrWas.ID < MrIs.ID)

Then you can just use MAX_REGISTERED rather than MONEDAS_VALORES in your query.

HTH,
Set