Are joins on an FK faster than joins without an FK?

Question

Say I have two tables, a and b:

a {
 pk as int
 fk as int
 ...
}

b {
 pk as int
 ...
}

I want to join a and b in a query like so:

FROM a
JOIN b on a.fk = b.pk

Which of the following scenarios will be faster?

1. a.fk is set up to be a foreign key on b.pk - b.pk is indexed
2. a.fk is set up to be a foreign key on b.pk - b.pk is not indexed
3. there is no relationship between the tables - b.pk is indexed
4. there is no relationship between the tables - b.pk is not indexed

Bonus question - how much faster/slower will each of these scenarios be?

If you could back up your answer with a reference then that'd be awesome. Thank you!

Mohenjodaro

Answer 1

Best practice

1. Foreign Keys are a relational integrity tool, not a performance tool. You should always create indexes on FK columns to reduce lookups. SQL Server does not do this automatically.
2. As stated here Foreign keys boost performance

Logically, this gives following ranking performance wise

1. a.fk is set up to be a foreign key on b.pk - b.pk is indexed
2. there is no relationship between the tables - b.pk is indexed
3. a.fk is set up to be a foreign key on b.pk - b.pk is not indexed
4. there is no relationship between the tables - b.pk is not indexed

Trant

Thanks! Very helpful. Any idea how big the performance difference between 1 and 2 (in your ranking) would be? – Mohenjodaro

Negligable if at all there would be a difference. – Trant

Link is broken. – Leguminous

Answer 2

The performance differnces would be greatest between the indexed and non indexed versions, however whether it would be faster or slower would depend on whether it was a select or an insert. Having indexes and foreign key constraints slow down inserts but speed up selects (the index) or make the data more reliable (the FK). Since generally most inserts are not noticably slowed (unless you are doing large bulk inserts), it is usually in your best interests to have the FK and the index.

Gresham

Answer 3

I'll ditto Lieven's answer. Just to reply to your bonus question of how much of a performance boost you get from creating an index, the answer is, "That depends".

If one or both tables are small and they are the only two tables in the query, the performance gain might be small to zero. When the number of records is small, sometimes it's faster to just read all the records rather than use the index anyway. The database engine should be smart enough to figure this out -- that's what "query optimization is all about".

Likewise, if you have other tables involved and other selection criteria, the DB engine may decide not to use this index, and that some other way of finding the records is faster.

At the other extreme, if you have two very large tables, creating an index on the field used to join them can cut run time by 99% or more.

That's why it's a good idea to learn to read the explain plans on your DB engine. If a query takes a long time, run the explain plan and see what it's doing. Often, creating a good index can dramatically improve a query.

Zinck