At 04:41 PM 12/7/2002 +0100, Arno Brinkman wrote:

>Hi,
>
>> > Wouldn't it speed up increadibly when we don't fetch the linked record,
>but
>> > only do an index evaluate ?
>
>So the record-pointer available by the index isn't transaction dependent ?
>

The record number from the index is transaction dependent. But since
multiple versions of that record can exist simultaneously in the
index and your transaction can see at most one of them, the
engine must fetch the appropriate record and verify that value
matches the selection expression.

>> So, Firebird can't optimize queries with index lookups by avoiding row
>> retrieval.
>
>That would be pitty :(
>

Actually, no. For a database system to use an index to compute
cardinality it must lock the entire index for the duration of
the operation. This is a disaster in a update intensive system.

When considering any optimization, you really must consider the
system cost, not just query cost. One could always improve
single user performance by locking other users for the duration
of a query/transaction. And you could win many benchmarks by
doing so. But the system would be unusable.

Jim Starkey