Does Foreign Key improve query performance?

Question

Suppose I have 2 tables, Products and ProductCategories. Both tables have relationship on CategoryId. And this is the query.

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category
FROM Products p
INNER JOIN ProductCategories c ON p.CategoryId = c.CategoryId
WHERE c.CategoryId = 1;

When I create execution plan, table ProductCategories performs cluster index seek, which is as expectation. But for table Products, it performs cluster index scan, which make me doubt. Why FK does not help improve query performance?

So I have to create index on Products.CategoryId. When I create execution plan again, both tables perform index seek. And estimated subtree cost is reduced a lot.

My questions are:

Beside FK helps on relationship constraint, does it have any other usefulness? Does it improve query performance?
Should I create index on all FK columns (liked Products.CategoryId) in all tables?

score 217 · Accepted Answer · edited Mar 16 '12 at 08:35

217

Foreign Keys are a referential integrity tool, not a performance tool. At least in SQL Server, the creation of an FK does not create an associated index, and you should create indexes on all FK fields to improve look up times.

edited Mar 16 '12 at 08:35

onedaywhen

55,269
12
100
138

answered Feb 03 '09 at 14:07

cmsjr

56,771
11
70
62

40

Good models (generally) perform better. – Kenny Evitt Mar 20 '11 at 03:03
10

"Foreign Keys are a relational integrity tool" -- please use the word 'relational' with care. Foreign keys are a database concept, a short hand for a referential integrity constraint. They are not part of the relational model. I assume you made a typo. – onedaywhen Mar 16 '12 at 08:34
8

@Kenny Often yes, but sometimes a better model costs more. Case in point: foreign keys cause more processing to happen, not less. – Hans Dec 06 '12 at 00:27
8

foreign keys *do* improve performance, at least in MySQL. Moreover, you're right, the creation of a FK does not create an index; the creation of a FK **requires** an index – Félix Adriyel Gagnon-Grenier Aug 26 '14 at 17:31
21

This answer is pretty much useless because it doesn't answer the question. It's great to know that foreign keys are not *intended* to have a (positive) effect on performance, but the question was concerning reality, not intentions. – John Mar 15 '17 at 16:04
@FélixGagnon-Grenier - Are you saying that indexes should be created on the owner of the foreign key (ie. the table with the primary key)? Or, should the index be on the table where you're looking for performance? – Scuba Steve Dec 06 '18 at 00:13
1

@FélixGagnon-Grenier I believe this is true only in MySQL where as in SQL Server you have to create it yourself – NeutronCode Dec 19 '18 at 12:54
1

@ScubaSteve My understanding and experience is that columns upon which a `JOIN` will be made or used in a `WHERE` should definitely be indexed, to sometimes dramatic effects, which implies that both owner and target table should be indexed. – Félix Adriyel Gagnon-Grenier Dec 19 '18 at 13:40
1

@ScubaSteve `index` are used to search values. Your primary key is already indexed, so the database will find this record quickly. Then it has to `JOIN` some other table based on a record (let's call it `id1`) from this first table. It already has `id1`, so you have to index the second table's column that contains `id1`: the "foreign key" column. – jpenna Sep 20 '19 at 23:05

score 69 · Answer 2 · edited Jun 05 '13 at 12:02

69

Foreign Keys can improve (and hurt) performance

As stated here: Foreign keys boost performance
You should always create indexes on FK columns to reduce lookups. SQL Server does not do this automatically.

Edit

As the link now seems to be dead (kudos to Chris for noticing), following shows the gist of why foreign keys can improve (and hurt) performance.

Can Foreign key improve performance

Foreign key constraint improve performance at the time of reading data but at the same time it slows down the performance at the time of inserting / modifying / deleting data.

In case of reading the query, the optimizer can use foreign key constraints to create more efficient query plans as foreign key constraints are pre declared rules. This usually involves skipping some part of the query plan because for example the optimizer can see that because of a foreign key constraint, it is unnecessary to execute that particular part of the plan.

edited Jun 05 '13 at 12:02

Andrzej Doyle

102,507
33
189
228

answered Feb 03 '09 at 14:14

Lieven Keersmaekers

57,207
13
112
146

3

Here's a link that details ways in which they can degrade performance http://www.devx.com/getHelpOn/10MinuteSolution/16595/0/page/2 – cmsjr Feb 03 '09 at 14:31
3

That makes sense but you'll only run into this with a massive delete statement. Perhaps the conclusion should be that in OLAP environments, non-indexed FK's would improve performance while in OLTP environments, it would degrade performance. – Lieven Keersmaekers Feb 03 '09 at 14:56
1

The link in this Answer is dead. This is unfortunate as it's the only argument here for FKs improving performance. – Chris Moschini Oct 11 '11 at 18:40
1

@ChrisMoschini - I didn't notice your comment until now. As you've mentioned, the link is dead but the gist of it is mentioned in the new link (with details) I've posted. – Lieven Keersmaekers Mar 16 '12 at 07:38
1

Thanks @Lieven; the summary of that article is more complex - that FKs increase Read performance and reduce Write performance. It includes basic tests to prove the Read gains, but none for Write. – Chris Moschini Jun 10 '12 at 22:49
@andrei.ciprian - that's what Chris noticed 4 years ago and why I included the gist of the article into the answer. :) – Lieven Keersmaekers Mar 01 '15 at 10:30
2

Wayback Machine [link](https://web.archive.org/web/20101219111457/http://www.microsoft.com/technet/abouttn/flash/tips/tips_122104.mspx) for the Win! The article can also be found on SQLMag.com, [here](http://sqlmag.com/database-performance-tuning/foreign-key-constraints-without-nocheck-boost-performance-and-data-integ). – John Eisbrener May 13 '16 at 16:23
1

Note that FKs only help with SQL plans in limited cases, where the optimizer can avoid scans on the parent table to verify existence of a reference from the child table. For instance, `select child.* from child join parent on child.parent_id=parent.id` can skip scanning `parent`. As soon as you have `select child.*, parent.foo from child join parent ...` the FK doesn't help. – wrschneider Jan 05 '17 at 22:26

score 19 · Answer 3 · edited May 12 '17 at 18:27

19

A foreign key is a DBMS concept for ensuring database integrity.

Any performance implications/improvements will be specific to the database technology being used and are secondary to the purpose of a foreign key.

It is good practice in SQL Server to ensure that all foreign keys have at least a non clustered index on them.

I hope this clears things up for you but please feel free to request more details.

edited May 12 '17 at 18:27

Matthew Haugen

12,916
5
38
54

answered Feb 03 '09 at 14:10

John Sansom

41,005
9
72
84

11

@Kenny Evitt if you don't have integrity, your data is useless. I find that sells very easily. – HLGEM Aug 24 '11 at 17:14
@HLGEM Getting a [404 error](http://en.wikipedia.org/wiki/HTTP_404) once in a while is still quite bearable. Having exceptional throughput in return using cheaper resources and less complex systems, now that sells very easily too. You might be interested in the [C.A.P. theorem](http://en.wikipedia.org/wiki/CAP_theorem). – Daniel Dinnyes Oct 19 '11 at 14:17
9

@Daniel Dinnyes, data integrity isn't about getting a 404 error. It's about having usable data. It's about not losing orders and financial data for reports for instance because of the incompetence of the developers. There is NO EXCUSE for not using foreign keys. – HLGEM Oct 19 '11 at 14:20
@HLGEM If 404 error is not about exchanging data integrity for simplicity (i.e. not having foreign keys) then I don't know what. Enforcing checks for referential integrity is an overhead which in certain dire circumstances is not affordable. – Daniel Dinnyes Oct 20 '11 at 00:56
2

I agree with HLGEM. Letting your code to handle integrity is not always a good idea. Data is often used to take decisions, but if the data is corrupted, then the decision will not be accurate. – lepe Jan 13 '12 at 01:29
1

"Foreign Keys are a relational integrity tool" -- please use the word 'relational' with care. Foreign keys are a database concept, a short hand for a referential integrity constraint. They are not part of the relational model. I assume you made a typo. – onedaywhen Mar 16 '12 at 08:35

score 4 · Answer 4 · answered Feb 03 '09 at 15:02

Your best performance bet is to use Indexes on fields you use frequently. If you use SQL Server you can use profiler to profile a specific database and take the file that outputs and use the tuning wizard to recieve recommendations on where to place your indexes. I also like using profiler to flush out long running stored procedures, I have a top ten worst offenders list I publish every week, keeps people honest :D.

score 3 · Answer 5 · edited May 18 '21 at 12:00

You can use it to help make a query more efficient. It does allow you to restructure queries in SQL Server to use an outer join instead of an inner one which removes sql servers necesity of having to check if there is a null in the column. You don't need to put that qualifier in because the foreign key relationship already inforces that for you.

So this:

select p.ProductId, p.Name, c.CategoryId, c.Name AS Category 
from Products p
inner join ProductCategories c on
p.CategoryId = c.CategoryId
where c.CategoryId = 1;

Becomes this:

 SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category 
 FROM ProductCategories c 
 LEFT OUTER JOIN Products P ON
 c.CategoryId = p.CategoryId 
 WHERE c.CategoryId = 1;

This won't necessarily make a huge performance in small queries, but when tables get large it can be more efficient.

Not only are outer joins typically _less_ efficient than inner joins (http://stackoverflow.com/a/2726683/155892), now your queries are misleading: you're relying on the database to implicitly turn your outer joins into inner joins (restoring the performance) instead of just doing that explicitly — Mark Sowul, May 03 '12 at 15:23

score 3 · Answer 6 · answered Feb 03 '09 at 15:09

I do not know much about SQL server, but in case of Oracle, having a foreign key column reduces the performance of data-loading. That is because database needs to check the data integrity for each insert. And yes, as it is already mentioned, having an index on foreign key column is a good practice.

score 3 · Answer 7 · answered Jun 18 '18 at 22:03

For MySQL 5.7, it definitely can speed up queries involving multiple joins amazingly well!

I used 'explain' to understand my query and found that I was joining 4-5 tables - where no keys were used at all. I did nothing but add a foreign key to these tables and the result was a 90% reduction in loadtime. Queries that took >5s now take 500ms or less.

That is an ENORMOUS improvement!

AND, as others have mentioned, you get the added bonus of ensuring relational integrity.

Beyond this, ensuring referential integrity has it's own performance benefits as well. It has the second order effect of ensuring that the tables that have the foreign key are 'up to date' with the foreign table. Say you have a users table and a comments table, and you're doing some statistics on the comments table. Probably if you hard delete the user, you don't want their comments anymore, either.

Did the tables have the required indexes to generate the foreign keys before you added them? — George, Mar 26 '20 at 13:48

score 3 · Answer 8 · answered Aug 28 '11 at 09:43

Adding a foreign key in table will not improve the performance, simply saying if you are inserting a record in a ProductCategories table database will try to find the foreign key column has a value which exist in a products table's primary key value, this look up, operation is overhead on your database every time you add a new entry in ProductCategories table. So by adding a foreign key will not improve your database performance but it will take care about the integrity of your database. Yes it will improve the performance of you db if you are checking integrity using foreign key instead of running many queries for checking the record is exist in database in your program.

score 0 · Answer 9 · answered Apr 20 '17 at 01:11

As of SQL Server 2008 foreign keys can influence performance by influencing the way the database engine chooses to optimise the query. Refer to Star Join Heuristics in the following article: https://technet.microsoft.com/en-us/library/2008.04.dwperformance.aspx

score 0 · Answer 10 · answered Jun 26 '21 at 18:10

YES, a FK can speed up SELECT but slow down INSERT/UPDATE/DELETE

SQL Server uses all constraints (FK included) to build better execution plans for SELECTs.

For instance, if you run a query with Column1 = X and X does not fit the constraint, the server won't even touch the table.

P.S. unless the constraint is in "untrusted" state, but that's a whole different story.

P.P.S. However having a foreign key (or other constraints) can slow down INSERT/UPDATE/DELETEs unless you have a non-clustered index on this column

Does Foreign Key improve query performance?

10 Answers10

Linked

Related