Re: Indexes and Foreign Keys

"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message news:_7JQf.55960$Jd.19562_at_newssvr25.news.prodigy.net...
> 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.
>

The most important thing to consider is not whether it is a FK, but is the FK used in a predicate of an SQL statement that can benefit from having an index created on those columns. But if all your tables have 1000 rows or less, it is doubtful than the indexes would improve performance.

In some cases, where you have a lot simultaneous updates to the tables, but each on different rows, then retrieving the row via an index can improve concurrency, even though it would not otherwise (without simultaneous updates) retrieve the row any faster. Received on Sat Mar 11 2006 - 17:43:56 CST