Re: Indexes and Foreign Keys

Creating indexes that will not be used wastes space and adds another index that has to be maintained to the target table.

Now any large table with a FK to a volatile (heavy update and delete) parent row will require an index on the FK column. Child tables to parent tables where the PK or UK is never updated and the partent rows are not deleted are highly unlikely to ever show up as a performance problem that traces back to the lack of an index to support the FK.

My experience says that a significant percentage of FK definitions can get away without an index to support DML activity on the parent, but if you do not know the parent DML activity or your are working on a product that will be used differently by different customers out in the field I would error on the side of adding the index.

IMHO -- Mark D Powell -- Received on Sat Mar 11 2006 - 16:26:31 CST