I have a Message class - a forum message, and it can be linked to itself through one-to-many communication, that is, a message can contain a set of other child messages (replies to the message), each answer can also contain a child of any nesting.

 public class Message { public int Id { get;set; } ... public int? ParentId { get; set; } public virtual Message Parent { get; set; } public virtual ICollection<Message> ChildMessages { get;set; } }

The question is whether it is necessary to put the [Index] attribute on the ParentId property, if I use recursion on ChildMessages when displaying forum ChildMessages ? That is, the operation ChildMessages very frequent.

The question came from here. Of course, I understand that for a quick search across the field, an index is needed, but on the other hand, if I declare a connection to many in EF, and I have the ChildMessages field ChildMessages then it is clear that I started it not just like that, but to search for it . Then EF could put down all the necessary indices and make the necessary optimizations.

  • 2
    Indices can not be put at random. Those. in this case, you will most likely guess the index, but in general it’s worth looking at real queries with a profiler, checking the plans for their execution - and only then putting the indices under them. - PashaPash ♦

1 answer 1

In my opinion, the attributes that participate in the relationships between entities should always have an index (If the tables are of course not miserable, then the DBMS will most likely ignore them).

In addition, you can always test the work with the index and without it, compare query plans and, based on this, make a conclusion whether there is any profit from it or not.

Even many database design environments automatically put indexes on such attributes.

But whether to put the index on other attributes is already found out by analyzing the query execution plans and so on.