> Alexandre Benson Smith wrote:
>
>>[snip] FB can't just use indexes
>>for lookup values, it has to go to the datapage to see if that record
>>are visible or not to the current transaction.
>

Kjell Rilbe wrote:

> Does FB cache this info in any way? I realize it might not be a good
> idea for lots of reasons but I can also imagine that with an ingenious
> solution it just might be a good idea after all. So, I'm curios how it's
> done today and if there are any future plans in this area.
>

If I understand correctly, and you're asking if Firebird caches the
valid transaction range for index key values, the answer is no. The
amount of stuff to cache and maintain seems, on superficial glance,
large, volatile, and hard to use.


For the particular query that the original writer presented:

select distinct my_field from my_table

the correct access strategy is very dependent on the number of distinct
values for my_field and distribution of records containing those values
in the database. This is one case where I'm tempted to suggest that the
solution might better reside in the application. If the design suggests
that there would be relatively few distinct values for my_field, I'd add
a table of distinct my_field values maintained with triggers on my_table
- an entry for each value and a use count. When an update or delete
trigger reduces the use count to zero, it deletes the record.

The original writer doesn't say how many distinct values are returned,
but does say "mytable has 2.1 million records with an index on store_no.
It takes 3.32 minutes to run [on Firebird]. On the database I want to
convert from, it takes 15 secs." My guess is that maintaining a
separate table of those values, and using the query "select distinct
my_field from my_field_values" would take less than a second.

Regards,


Ann