Sql-server – Performance effect of indexing foreign keys

database-designforeign keyperformancesql serversql-server-2012

My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key.

There are two tables involved. The first is a ratings table with 2 columns: ID (Integer, Primary Key) and Description (Varchar). It will only have 4 rows (poor, average, above average and excellent) and will "never" change.

The second table is a review table. There will be 1 row for each employee every six months. So we are looking at thousands of entries and not millions. Each row has approximately 25 evaluation fields, which are foreign keys into the rating table. Examples would be typical questions where employee is rated on Completing Work on Time, Works well with others etc…

From what I've researched, there doesn't appear to be any real reason to create an index on each of those keys since I will never be adding and/or deleting from the ratings table. Are there any other considerations that I should take into account? Or am I worrying about nothing since by db is so small?

Best Answer

It may still be useful to have indexes on the larger table to help the optimizer for specific SELECT queries, even if you will "never" change any of the values.

However, without a lot more knowledge of your system and the types of queries you will run, it's pretty difficult for any of us to able to tell you whether you should have indexes there or not. I'm afraid it's just something you're going to have to test.

Some background in this great article by Erin Stellato:

http://www.sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys

You also might consider avoiding the joins in some cases - when you are sure the values will never change, and eliminating the join can simplify the execution plan, you can consider just inline constants, e.g.

SELECT CASE RatingID
  WHEN 1 THEN 'poor'
  WHEN 2 THEN 'average'
  ... 
END
FROM dbo.BiggerTable
...

Also I suggest never naming something ID. If it's a RatingID, call it RatingID everywhere it exists in the model.

Related Question