I've run into this type of problem before (and not just with Interbase).

One workaround I came up with (on MS SQL) was using an outer join to relate
the parent and child tables instead of an inner join. This caused the
optimizer to not try and use the index to select which records to use, and
the query ran many, many times faster.

It would be nice if the optimizer could just be smarter. For example, it
could use the foriegn key information to realize that:

(1) Joins between the child and the parent will NEVER reduce the number of
records (by definition) and should be used for lookup only, and executed
only after all other sub-selections.

(2) If there's a subselection on the parent (or directly on the field in
the child), then it MAY be efficient to use the index, but only if the
parent table has at least a minimal size (e.g. if the parent only has 3
records, and the child has 3,000,000 then the index is not going to be of
much use).

For small tables that easilly fit in memory (say < 500 records) there's
probably no point in using (or maintaining) indexes.

For foriegn keys that relate to very small tables a similar argument
applies.




-----Original Message-----
From: Ann Harrison [mailto:harrison@...]
Sent: Tuesday, April 04, 2000 12:53 PM
To: IB-Architect@egroups.com; IB-Architect@egroups.com
Subject: [IB-Architect] Foreign Key indexes


At 12:37 AM 4/4/00 -0400, Claudio Valderrama C. wrote:

> 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.

Here are some questions about foreign key indexes.

Right now indexes are automatically created for every
foreign key. Those indexes can not be dropped and
require some backhand magic to rename. As a result, if
the parent table is small and the child table large, the
foreign key index has very poor selectivity and can have
a terrible effect on performance.

1) The optimizer should be improved so that it doesn't
use really terrible indexes, right?

2) Does it make sense to maintain indexes that can never
be used?

3) Should InterBase just get out of the business of
creating indexes on foreign keys?

4) If InterBase creates indexes on foreign keys, should
it allow them to be dropped?

Cheers,

Ann



------------------------------------------------------------------------
Special Offer-Earn 300 Points from MyPoints.com for trying @Backup
Get automatic protection and access to your important computer files.
Install today:
http://click.egroups.com/1/2344/3/_/_/_/954874328/
------------------------------------------------------------------------

To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com