Re: Deleting vs foreign keys

On Tue, 2005-10-25 at 11:18, WireSpot wrote:
> On 10/25/05, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> > Are there indexes on the foreign key columns? That is, given
> > the following example,
> >
> > CREATE TABLE foo (id integer PRIMARY KEY);
> > CREATE TABLE bar (fooid integer NOT NULL REFERENCES foo ON DELETE CASCADE);
> >
> > do you have an index on bar.fooid?
>
> There are no indexes on the referring fields in any tables. Would this
> make a huge difference?

Yes it will. Think about it: when the delete cascades, the rows which
have to be deleted in the referring table have to be found first... if
there's no index, a full table scan will be done for that. An index will
allow the delete process to quickly find the referring rows which have
to be deleted too. So it's a good practice to create indexes on fields
referring to parent tables.

Cheers,
Csaba.