Advanced Indexing for Performance

In this lesson, you will learn how to use query execution plans to identify when Filtered and Included indexes can surgically optimize your EF Core performance.

The Problem: Beyond the B-Tree

Standard indexes (B-Trees) are excellent for searching, but they often require a "Key Lookup" to fetch non-indexed columns from the main table, which is expensive for large datasets. To eliminate these lookups and minimize index bloat, we use two advanced techniques: Filtered Indexes and Included Columns.

Included Columns (Covering Indexes)

If your query frequently retrieves Title and CreatedDate by filtering on UserId, a standard index on UserId forces the database to jump to the table heap for every result. By adding Include to your index, you "cover" the query, allowing the database to return all data directly from the index structure.

In EF Core's Fluent API, you define this as:

modelBuilder.Entity<Post>()
    .HasIndex(p => p.UserId)
    .IncludeProperties(p => new { p.Title, p.CreatedDate });

Filtered Indexes

Sometimes, you only care about a subset of data. For example, if you frequently query "Active" orders, indexing the entire Orders table (including thousands of "Cancelled" or "Archived" rows) is a waste of memory and disk I/O. A filtered index keeps the index small and fast by only indexing rows that meet your criteria:

modelBuilder.Entity<Order>()
    .HasIndex(o => o.CustomerId)
    .HasFilter("[Status] = 'Active'");

Analyzing the Profile

When you profile your queries (using tools like MiniProfiler or SQL Server Management Studio), look at the Execution Plan.

1. If you see a Key Lookup (Clustered) operator, the database is struggling to find "extra" columns; consider an Include index.
2. If the Index Seek operation is slow despite having an index, check if the index size is massive due to irrelevant data; a Filtered Index may be the solution.

By aligning your indexes with the specific shapes of your most common queries, you move from "indexing everything" to "indexing intelligently."

Key Takeaway: Use Included columns to satisfy query payloads entirely within the index, and use Filtered indexes to ignore irrelevant rows, drastically reducing index size and lookup overhead.