Does Foreign Key improve query performance?

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:

1. Beside FK helps on relationship constraint, does it have any other usefulness? Does it improve query performance?

2. Should I create index on all FK columns (liked Products.CategoryId) in all tables?

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.

Foreign Keys can improve (and hurt) performance

1. As stated here: Foreign keys boost performance

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

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.

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.

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.

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.

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.

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.

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

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