To decide if a foreign key needs to be indexed or not, I follow a simple rule:
I always/only create an index on a foreign key whether:
1 - A deletion on the parent table is allowed and it triggers a cascade delete on the child table
2 - There's need to perform JOIN queries from the parent to the child
In the first situation, an unindexed foreign key will force a full table scan for each parent record deleted. In the second situation, a lack of the foreign key index in the child table will slow down join queries.
I rarely find suitable to create indexes on foreign keys in other situations though.
Subscribe to:
Post Comments (Atom)
2 comments:
I think the question is not only “to index or not to index” on foreign keys.
I have been debating this subject with my boss (a hardcore Informix believer) today. We found that Informix has always created indexes (that you actually cannot drop) on foreign keys automatically, but none of Oracle, DB2, and SQL Server do it.
So, why some engineers decided to go one way and others in the opposite? I see a very interesting design decision there.
Informix won’t give you a choice, taking responsibility in optimizing JOINs and CASCADING operations on the foreign key.
However, in many cases a composite index that is headed by the foreign key but also includes other columns relevant to frequent queries is superior to a mere foreign key index.
So, in Oracle, DB2 or SQL Server you can choose to create this kind of index (actually the responsibility of doing so is yours), which theoretically could give you better performance than Informix (obviously, my boss won’t swallow that pill!).
I think an important conclusion is that you should always tune your indexes globally, by using real profiling data.
Fortunately, those of us using SQL Server can count on the Index Tuning Wizard. Are there similar tools for Oracle and DB2?
Thanks diego for expanding the subject with your experience. I should have stated that I usually use PostgreSQL and have perceived a good behavior following the simple rule described in here. Hope someone else will answer your question about profilers for Oracle and DB2.
Post a Comment