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.

Answer 2

Short answer. No.

To expand slightly, at the database create time, entity framework does not know how many records each table or entity will have, nor does it know how the entities will be queried.

*In my opinion * the creation of a foreign key is more likely to be right than wrong, I had massive performance issues using a different ORM which took longer to diagnose because I thought I had read in the documentation that it behaved the same way.

You can check the Sql statement that EF produces and run it manually if you want to double check.

You know your data better than EF does, and it should work just fine if you drop the index manually.

IIRC you can create 1 way navigation properties if you use the right naming convention, although this was some time ago, and I never checked whether the index was created.

Answer 3

The accepted answer insufficiently answers the question at hand, so I'm going to give it another go.

The direct answer to the question is that: High performance database design will always prefer having indexes on Foreign Keys because they're used during the check process for inserts, updates and deletes.

When any data is being inserted, updated or deleted constraints must be checked against existing foreign keys.

The OP is solely focused on the query patterns which misses the point. Indexes in this case are dual purpose: query patterns (which are not at play in this scenario) and FK constraint maintenance.

Many production apps have suffered by doing deletes on tables with FKs that are missing corresponding indexes. The data set and cascading delete just has to be large enough.

Here's a decent resource to read further - https://www.dataversity.net/foreign-keys-and-the-delete-performance-issue/

As the article mentions,

There is a very basic, yet very common misunderstanding set by developers and beginner modelers

and unfortunately the opinionated conversation has ensued from this. There is no opinion when it comes to good database design principles that must be followed or suffer the performance consequence.

Answer 4

Change the conflict FK (Foreign Key) name in ApplicationDbContextModelSnapshot file with another one. Then add migration again. It will override to it and not gonna give error.