sql - Entity Framework Indexing ALL foreign key columns

Question

This may be too much of an opinion-based question but here goes:

I've found an interesting quirk with Entity Framework and database migrations. It seems that whenever we create a foreign key it also creates an index on that column.

I read this SO question: Entity Framework Code First Foreign Key adding Index as well and everyone seems to say it's a great, efficient idea but I don't see how; indexing a column is very circumstance-specific. For instance, EF is indexing FKs on my table that are almost never (~1%) used for searches and are also on a source table, meaning that even when I join other tables, I'm searching the FK's linked table using it's PK...there's no benefit from having the FK indexed in that scenario (that I'm aware of).

My question:

Am I missing something? Is there some reason why I would want to index a FK column that is never searched and is always on the source table in any joins?

My plan is to remove some of these questionable indexes but I wanted to to confirm that there's not some optimization concept that I'm missing.

Answer 1

In EF Code First, the general reason why you would model a foreign key relationship is for navigability between entities. Consider a simple scenario of Country and City, with eager loading defined for the following LINQ statement:

var someQuery = 
   db.Countries
     .Include(co => co.City)
     .Where(co => co.Name == "Japan")
     .Select(...);

This would result in a query along the lines of:

SELECT *
FROM Country co
INNER JOIN City ci
  ON ci.CountryId = co.ID
WHERE co.Name = 'Japan';

Without an Index on the foreign key on City.CountryId, SQL will need to scan the Cities table in order to filter the cities for the Country during a JOIN.

The FK index will also have performance benefits if rows are deleted from the parent Country table, as referential integrity will need to detect the presence of any linked City rows (whether the FK has ON CASCADE DELETE defined or not).

TL;DR

Indexes on Foreign Keys are recommended, even if you don't filter directly on the foreign key, it will still be needed in Joins. The exceptions to this seem to be quite contrived:

• If the selectivity of the foreign key is very low, e.g. in the above scenario, if 50% of ALL cities in the countries table were in Japan, then the Index would not be useful.

• If you don't actually ever navigate across the relationship.

• If you never delete rows from the parent table (or attempt update on the PK) .

One additional optimization consideration is whether to use the foreign key in the Clustered Index of the child table (i.e. cluster Cities by Country). This is often beneficial in parent : child table relationships where it is common place to retrieve all child rows for the parent simultaneously.