Jim:
The main point is not the FK declaration itself (I will be surprised if the
optimizer knew about it), it's the index that's created automagically as
part of the FK that confuses the optimizer. Getting rid of that index makes
the query run much faster. Just to clarify what the user said in
interbase.public.general:

austin wrote in message <8c9h31$9e8$1@...>...

>Hi,
>Yes, I have the date indexed. I believe I have the necessary indexes. Its
>just that IB used the foreign key index ie. RDB$FORIEGN140 which is on the
>table TxClaimResult.TxKey, refers to Tx.PkId. There is an index on
>TxClaimResult.TxKey.
>Once I removed the foreign key, IB used the index on TxClaimResult.TxKey ,
>and the query worked faster. I do not understand why IB used the foreign
>key index, I was looking for an explanation on this.
>I do not want to have to specify a plan, so I will play around using
>explicit joins, and the order of the joins. If the ordering of joins
>matters, how should I proceed ?. I assumed I would use the date first
> refer to original e-mail with SQL listed ), which will narrow the result
>set immediately.
>Thanks.
>Austin

And the user is trying to proceed exactly as we would want: without caring
about the plan nor he wants to fiddle with such "facility".

;-)

C.
---------
Claudio Valderrama C.
Ingeniero en Informática - Consultor independiente
http://members.xoom.com/cvalde

> -----Original Message-----
> From: Jim Starkey [mailto:jas@...]
> Sent: Lunes 3 de Abril de 2000 9:51
> To: IB-Architect@onelist.com
> Subject: Re: [IB-Architect] Fw: Sql Speed - Interbase using Foreign Key
> index , not the 'regular' index
>
>
> At 09:02 PM 4/1/00 -0400, you wrote:
> >Hello, I think this is one to read... but only for people with
> enough time
> >to spend. I prefer the explicit join syntax but the point is: a
> foreign key
> >makes IB pick the wrong index. I suggested changing a condition
> of the form
> >tableA.field = tableB.field
> >that uses the wrong index to be rewritten as
> >not tableA.field <> tableB.field
> >so it gets no priority from the optimizer.
> >
>
> I don't know where you got the idea that the optimizer knows or
> cares about foreign keys. It doesn't. All it knows about are
> indices and whether or not they are unique.
>
> Jim Starkey
>
>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com
>
>
>