Do I need to index on a id in EF Core if I'm searching for an id in 2 different columns?

Question

If I do a query like below where I'm searching for the same ID but on two different columns. Should I have an index like this? Or should I create 2 separate indexes, one for each column?

modelBuilder.Entity<Transfer>()
  .HasIndex(p => new { p.SenderId, p.ReceiverId });

Query:

var transfersCount = await _dbContext.Transfers
    .Where(p => p.ReceiverId == user.Id || p.SenderId == user.Id)
.CountAsync();

What if I have a query like this below, would I need a multicolumn index on all 4 columns?

var transfersCount = await _dbContext.Transfers
.Where(p => (p.SenderId == user.Id || p.ReceiverId == user.Id) &&
      (!transferParams.Status.HasValue || p.TransferStatus == (TransferStatus)transferParams.Status) &&
      (!transferParams.Type.HasValue || p.TransferType == (TransferType)transferParams.Type))
.CountAsync();

Answer 1

I recommend two single-column indices.

The two single-column indices will perform better in this query because both columns would be in a fully ordered index. By contrast, in a multi-column index, only the first column is fully ordered in the index.

If you were using an AND condition for the sender and receiver, then you would benefit from a multi-column index. The multi-column index is ideal for situations where multiple columns have conditional statements that must all be evaluated to build the result set (e.g., WHERE receiver = 1 AND sender = 2). In an OR condition, a multi-column index would be leveraged as though it were a single-column index only for the first column; the second column would be unindexed.