Entity Framework Indexing All Foreign Key Columns

Question

It may be too many opinions-based questions, but it says:

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

I read this SO question: Entity Framework Code The first foreign key adds an index as well , and everyone seems to say that this is a great, effective idea, but I don't see how; column indexing is very specific. For example, EF indexes FKs in my table, which are almost never (~ 1%) used for searching, but are also in the original table, which means that even when I join other tables, I look at the table associated with FK using its PK ... there is no benefit to indexing FK in this scenario (what I know).

My question is:

Am I missing something? Is there any reason why I would like to index an FK column that never runs and is always in the source table in some joins?

My plan is to remove some of these dubious indexes, but I wanted to confirm that there is not any optimization concept that I am missing.

Answer 1

In EF Code First, the common reason you should model a foreign key relationship is because you can navigate between entities. Consider a simple Country and City script with impatient downloads defined for the following LINQ statement:

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

This will 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 the foreign key index in City.CountryId SQL, you will need to scan the city table to filter the cities for the country during JOIN.

TL DR

Pointers to foreign keys are recommended , even if you do not filter directly by foreign key, you will still need it in Joins. The exceptions to this seem to be very far-fetched:

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

• If you don’t navigate relationships at all.

Another optimization consideration is whether to use the foreign key in the Clustered Index child table (i.e., a cluster of cities by country). This is often useful in parent-child relationships: child tables, where the usual place is to get all the child rows for the parent at the same time.

Answer 2

The short answer. No.

To expand a little, at the time of creating the database, the entity structure does not know how many records each table or object will have, and does not know how objects will be requested.

* In my opinion, creating a foreign key is more likely to be right than wrong, I had serious performance problems using another ORM, which took longer to diagnose, because I thought I read in the documentation what it led yourself in 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, and this should work fine if you drop the index manually.

IIRC, you can create unidirectional navigation properties if you use the correct naming convention, although that was some time ago, and I never checked if this index was created.