Performance effect of indexing foreign keys

Question

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?

Answer 1

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.

Answer 2

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

Generally speaking, if the column will appear in a search condition such as a WHERE clause or a JOIN predicate, then it should probably be indexed. It depends on the exact queries that access the table.

In a greenfield project, my approach is usually to not create any supplementary indexes until I know they're necessary by analyzing the query plan of new queries as they're written. Most of the time, index-backed constraints used to ensure data integrity are sufficient to serve most queries efficiently.

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.

Unless there are mitigating circumstances, I suggest using a tinyint for the primary key of this table, as this will save you a bunch of storage space in the much larger table(s) that reference this table. (Maybe not much in absolute terms for this project, but in general, this is a good idea.)

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

I would suggest there is some work to be done to normalize this design. From what you've said, I can identify at least 5 tables involved:

Employees
Questions
Ratings
EmployeeEvaluations
EmployeeEvaluationQuestionRatings

The normalization work should be done first, and I think you'll find the question about indexing and performance largely goes away -- or becomes much more obvious -- after adding appropriate table constraints.