indexing foreign key columns

Question

indexing foreign key columns

ramakrishna.pujari

Valued Member

Points: 63
More actions
October 20, 2008 at 8:11 am

#199813

Hi,
if a table having 100,000 rows or more has a foreign key pointing to a lookup table of around max 10 or 20 rows, then is it worth creating an index for that foreign key? does it make any difference? I have read some posts saying creating index on foreign key is beneficial, but not sure if it is true in this kind of scenario. I have some tables like this in my app with foreign key pointing to small lookup tables.
regards,
RK

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

I don't think that there is a clear cut answer to your question. It will depend on the queries you are issuing, the data contained in the tables, existing indexes, etc. My suggestion would be to look at query execution plans for common queires you issue in the application with and without the index to determine its value.

That being said, my assumption is you will find little improvement with a table that has 10~ rows. If you are adding the index just so that finding the lookup value will be faster it may not be beneficial as SQL most likely still just perform a table scan of the lookup table because its more efficient then traversing the index structure for such a small set of records.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Maybe. Try an index there and see if SQL uses it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramakrishna.pujari Valued Member Points: 63 More actions · Answer 3

most of the foreign key columns are not null and most of these tables having foreign keys have views which join to these lookup tables always and I use views instead of tables in my queries wherever possible. so from your assumption it probably makes sense to avoid creating an index on these tables. however I haven't done any analysis by checking execution plans etc. I don't understand execution plans that much, but I can certainly run some sample queries using the indexed columns that return large amount of data and test the performance.

thanks for the reply.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

I don't understand execution plans that much,

It is very important to understand execution plan.

To start with, visit http://msdn.microsoft.com/en-us/library/ms979196.aspx

fruitloop SSC Enthusiast Points: 156 More actions · Answer 5

As per most SQL performance questions, it depends? is generally the answer.

As a rule of thumb in my current environment we put indexes on all foriegn keys.

This is because we are generally joining or filtering tables based on on the foriegn key values.

Also remember that views will utilise any table indexes if the optimiser thinks there will be a performance benefit.

At the end of the day the best plan would be to try out some indexes and typical queries in Development and see if you get a performance boast.

Kindest Regards,

Brandon