Re: Indexes and Foreign Keys

Okay this is basically the counter view I read about not necessarily needing indexes on all foreign keys.

So basically, the rule of thumb should be if the parent table is pretty much static then indexes on the foreign keys really don't add anything. But if the parent table will be volatile then they should be considered. Have I paraphrased that correctly?

Right now the small application I'm working on I can't imagine any of the tables having more than 500-1000 rows at all. The data is going to be downloaded from another program so no new rows will be added - just existing rows updated. From what I've read here it seems I could do away with most, if not all, of the indexes for the foreign keys with primary keys and unique keys sufficing for my needs.

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:1142115991.869498.239820_at_i39g2000cwa.googlegroups.com...
> 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 - 17:34:18 CST