There is a table in SQL Server 2008 R2:

enter image description here

Three columns, three indexes.

It was created using the Entity Framework, everything is simple:

public class Order { public int Id { get; set; } public int GroupBuyingId { get; set; } public GroupBuying GroupBuying { get; set; } public int ParticipantId { get; set; } public Contact Participant { get; set; } }

After some time, the demand came to impose a unique constraint on the GroupBuyingId and ParticipantId fields, so I did:

 public class OrderConfiguration : IEntityTypeConfiguration<Order> { public void Configure(EntityTypeBuilder<Order> builder) { builder.HasIndex(x => new { x.GroupBuyingId, x.ParticipantId }).IsUnique(); } }

But before applying the migration, he automatically looked at what was created there:

 public partial class UniqueConstraintForOrder : Migration { protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.DropIndex( name: "IX_Orders_GroupBuyingId", table: "Orders"); migrationBuilder.CreateIndex( name: "IX_Orders_GroupBuyingId_ParticipantId", table: "Orders", columns: new[] { "GroupBuyingId", "ParticipantId" }, unique: true); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.DropIndex( name: "IX_Orders_GroupBuyingId_ParticipantId", table: "Orders"); migrationBuilder.CreateIndex( name: "IX_Orders_GroupBuyingId", table: "Orders", column: "GroupBuyingId"); } }

And that's what I do not understand. A new index is created for two fields - this is correct. But for some reason one of the existing indices is demolished (by the way, why not both?), Apparently on the basis that it would be possible to use the composite index?

What is generally recommended in such cases - to leave one composite index or a composite + two separate ones?

It is clear that in the second case the volume occupied by the index will be more, plus, respectively, the time for processing it when inserting new values, but what about the speed of the samples?

Will a composite index work as efficiently as a single index?

    2 answers 2

    The difference for a composite index and a single-field index lies in the key length; the longer the key, the more read operations for a large table will have to be performed by the engine.

    In the above example, there is no point in keeping the index double, since the difference in key length is insignificant, there will be no practical gain from reading.

    A search always uses the first key field of the index, so the second index is not deleted, since it is built on a different (not the first in the composite) field.

    • Why are there more read operations when increasing the key length? the number of nodes in a binary search tree is the binary logarithm of the number of unique values ​​in the index, regardless of their length. (If you do not consider the list of rows matching the key itself, the size of which is in any case directly proportional to the number of table entries) Yes, of course, longer keys occupy more space in the index pages, but this will result in the need for additional readings with a really long key length. - Mike
    • I mean, of course, the number of nodes that need to be passed to search tree height - Mike
    • It seems that it is written: “In the above example, there is no point in keeping the index double, since the difference in key length is insignificant, there will be no practical gain in reading” - Denis Rubashkin
    • It is written more or less correctly. It's just that the phrase itself about the direct dependence of the number of reads on the key length is not correct. Even if the key length was 20 and 100 bytes, but adding a second field would not greatly increase the uniqueness, the number of readings for getting tree nodes would not have changed. If you have an index with the values ​​1, 2, 3, 4 and the index with "aaaaaaaaaaa", "bbbbbbbbbbbb", "cccccccccc", "dddddddddd" then the index levels will still be only 2, the root dividing the total key space approximately in half and Two pages of leaves of the left and right half - Mike

    A composite index by fields (A, B) can always be used instead of index (A), but cannot be used instead of index (B). Well, if this is the standard B-Tree index.

    However, a search by a simple index (A) will be slightly faster than by a composite (A, B), due to the greater number of entries in one key node of the tree. But on intah you will not notice.