Index on every Foreign Key?

Does Index on every Foreign Key makes queries optimized ?? 开发者_JAVA百科

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.

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.

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.