Index Optimization for Query Performance

Create simple and composite database indexes to accelerate data retrieval.

In this lesson, you will learn how to configure database indexes within Entity Framework Core to drastically speed up data retrieval for common query patterns.

Why Indexes Matter

Imagine trying to find a specific word in a 500-page book without an index. You would have to scan every single page from start to finish—this is what a database calls a "Full Table Scan." By adding an index, you create a sorted reference point (like an index at the back of a book) that allows the database engine to locate specific rows instantly.

Creating Simple Indexes

You can define an index using the Fluent API within your DbContext class. This is the preferred way to keep your entity classes clean while optimizing performance. For example, if you frequently query users by their Email address, you should index that column:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .HasIndex(u => u.Email)
        .IsUnique(); // Optional: ensures no two users have the same email
}

Creating Composite Indexes

Sometimes, you perform queries based on multiple columns simultaneously, such as searching for an Order by both CustomerId and OrderDate. A single index on one of these might not be enough. You can create a Composite Index to handle these multi-column lookups:

modelBuilder.Entity<Order>()
    .HasIndex(o => new { o.CustomerId, o.OrderDate });

When you define a composite index, the order of the properties matters. The database engine uses the index from left to right. If you frequently query by CustomerId alone, this composite index will help, but if you only query by OrderDate, the database might ignore this specific index.

While indexes make reads faster, remember that they add a small "cost" to write operations (inserts, updates, and deletes) because the database must update the index structure every time the data changes. Use them strategically on columns that are frequently used in Where clauses or OrderBy operations.

Key Takeaway: Use indexes to transform linear search times into near-instant lookups, but apply them selectively to columns frequently utilized in query filtering to balance read performance with write overhead.