Re: Indexes and Foreign Keys

On Sat, 11 Mar 2006 14:26:31 -0800, Mark D Powell wrote:

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

The "indexed FK" lore applies only to OLTP databases. The common wisdom, which I subscribe to, is always to index foreign keys. Yes, you might be wasting some resources, but if you don't do that, you are risking deadlocks and a serious downtime. I will gladly waste some resources to prevent that from happening, even if it means few MB and CPU cycles wasted. There is so called 80:20 law which says that in order to achieve 80% improvement, you need 20% of the effort needed for 100% improvement. The corollary is that for remaining 20% one needs 80% of the effort. I have better things to do then to worry about deadlocks, if I can prevent them. Even if the table is not a significant risk today, nobody guarantees that the purpose of the table will not be changed tomorrow.

-- 
http://www.mgogala.com