CODEWITHSUNDEEP
sqlsql-servert-sql
Sudantha
Sudantha

Reputation: 16214

Index on every Foreign Key?

Does Index on every Foreign Key makes queries optimized ??

Upvotes: 3

Views: 446

Answers (3)

RC.
RC.

Reputation: 28227

Typically it's considered good practice to place indexes on foreign keys. This is done b/c it helps with join performance when linking the FK table to the table that contains the definition of the key.

This doesn't magically make your entire query optimized, but it will definitely help to improve the join performance between the FK and it's Primary Key counter-part.

Upvotes: 12

DForck42
DForck42

Reputation: 20357

i did a little bit of testing on this, and i didn't find any performance enhancement, but SQLMenace will tell you otherwise. My opinion is to try it and see if it works for you.

Upvotes: 0

Jason
Jason

Reputation: 4695

It might be a seen as a good practice to add an index on every foreign key, but you should be warned that if you have a large database, the more index you have, the more heavy you system will become. There are always extra maintenance and system resource cost required when adding an index.

I personally would add indexes only on the foreign keys that are used in queries that needs optimization. Be sure to keep your indexes up to date by occasionally running a profiler to monitor your system.

Upvotes: 0

Related Questions