Key Points
- Index selectivity measures how effectively an index narrows rows for a query, directly influencing whether the query optimizer uses it.
- Highly selective indexes improve performance by reducing rows scanned, lowering CPU and I/O usage, and enabling index seeks over table scans.
- Index cardinality counts distinct values in a column, while index selectivity measures how useful those values are for filtering query results.
- Low-selectivity indexes are not automatically ineffective, as filtered, composite, and covering indexes can still reduce query work in the right context.
- SQL Server evaluates selectivity through statistics and cost models, making execution plan analysis and statistics maintenance essential to indexing performance.
- Evaluating index selectivity requires comparing distinct value counts, reviewing execution plans, confirming current statistics, and monitoring write overhead.
Indexes are reliable tools that help improve query performance, but they aren’t always optimized well enough for a specific query to deliver the desired results. Index selectivity explains most of this variation, describing how effectively an index narrows the rows a database needs to examine to satisfy a query. Understanding this helps database administrators make better decisions about which indexes to build, which to consider, and how to interpret what an execution plan is actually showing them.
What index selectivity is
Index selectivity measures how effectively an index narrows the rows that a database must examine when a query runs. The more precisely an index filters data, the less work the database has to do to return a result, and the more useful that index becomes in practice.
Selectivity matters because it directly influences how the query optimizer behaves. When an index is selective enough to reduce the result set appropriately for an intended purpose, the optimizer has a good reason to use it. When it isn’t, the optimizer may find a table scan more practical than following the index at all.
Index selectivity vs index cardinality
Index selectivity and index cardinality are related, but they’re not the same thing. Cardinality refers to the number of distinct values in an indexed column. So a column with many unique values has high cardinality, while one with a handful of repeated values has low cardinality. On the other hand, selectivity describes how useful that uniqueness actually is for filtering rows in a specific query. Basically, a column can have high cardinality without being very selective, and a low cardinality column isn’t automatically useless.
Here are a few examples showing where the two concepts diverge:
- A user ID column typically has high cardinality and high selectivity, as each value points to very few rows.
- A boolean column has low cardinality and low selectivity, because most queries will match a large share of the table.
- A date column may have high cardinality but low selectivity if queries commonly search across wide date ranges.
- A status column may have low cardinality but still contribute when paired with other columns in a composite index.
Ultimately, cardinality describes the data, while selectivity describes how effectively an index filters results for the queries that actually run against it.
How index selectivity affects query performance
Selectivity determines how much work an index actually eliminates. A highly selective index can:
- Limit how many rows the database needs to scan
- Allow the engine to seek directly to relevant rows rather than scanning broadly
- Cut down on CPU and I/O consumption
- Speed up filtered queries by narrowing results early
- Make join operations more efficient by locating matching rows faster
A low selectivity index may not help when:
- The query returns a significant portion of the table anyway
- Too many rows carry the same indexed value
- A full table scan turns out to be the cheaper path for the optimizer
- The overhead of following the index outweighs the rows it actually filters out
- The index doesn’t match the query predicate or join pattern
How an index performs depends mainly on how the database engine interprets it. In SQL Server, the optimizer uses statistics, cardinality estimates, and cost models to decide whether an index is worth using. For each query, it estimates:
- How many rows match the predicate
- Whether a seek or scan is more efficient
- Whether key lookups are required
- Whether a table scan may actually be cheaper
- Whether the available statistics still reflect the current data distribution.
Execution plans are the clearest way to see how those decisions play out, as they show exactly which access method the optimizer chose and why.
Outdated statistics can skew these estimates, so statistics maintenance should be treated as part of any indexing review. It’s also worth noting that poorly chosen indexes slow writes. Every insert, update, or delete must maintain the index, so one that doesn’t reduce query work becomes a liability.
High and low selectivity: Examples and exceptions
Indexes will vary, and selectivity won’t always be obvious from a column’s data type or label alone. Consider the examples below that show where selectivity tends to be strong, where it can fall short, and where the line isn’t always clear.
High-selectivity columns that map each value to very few rows:
- Customer or user IDs
- Email addresses
- Transaction or order IDs
- Serial or account numbers
- Device identifiers
Low-selectivity columns that match large portions of the table:
- Active or inactive status flags
- True or false columns
- Priority levels with only a few values
- Region or category fields with uneven distribution
It’s important to note that low cardinality doesn’t automatically disqualify an index. In the right context, a low-selectivity column can still contribute when used in:
- Filtered indexes: Useful when targeting rare values like failed or pending records
- Composite indexes: Combining a status column with a date and customer ID can meaningfully narrow results
- Covering indexes: Eliminate extra lookups even when the leading column isn’t highly selective
- Skewed distributions: A value that appears rarely across a large table can still benefit from an index
The real question isn’t whether a column has low cardinality but whether the index reduces enough work for the queries that actually run.
Another crucial thing to remember is that column order matters when multiple columns are combined into a single index. The database typically evaluates the leading column first, so the most selective or most commonly filtered column should come first. A well-ordered composite index can:
- Turn multiple low-selectivity columns into a highly selective combination
- Support common query filter patterns more efficiently
- Cover sorting and joining needs in a single index
For example, a status column alone may not be selective enough to justify an index. But an index on status, created_date, and customer_id can be genuinely useful if queries regularly filter by all three.
How to evaluate index selectivity
To evaluate selectivity, one must look at both the data and how queries behave against it. Consider doing the following for a practical check that will let you know if an index really contributes to query performance:
- Compare the number of distinct values in an indexed column against the total row count
- Look for skewed distributions where one value dominates the rest
- Review execution plans for seeks, scans, key lookups, and table scans
- Confirm the index aligns with actual WHERE, JOIN, ORDER BY, and GROUP BY patterns
- Compare query performance before and after index changes
- Check index usage statistics where available
- Verify that statistics are current
- Monitor write overhead after adding new indexes
Make sure to evaluate selectivity as part of a broader indexing strategy and not in isolation, because an index that looks reasonable on paper may behave differently against real query patterns and live data distribution.
Common signs and mistakes
Recognizing poor index selectivity early should help prevent performance issues from piling up over time. Here are some common signs to look out for, indicating that an index may not be selective enough:
- Index scans appearing where seeks would be expected
- Table scans on large tables, despite indexes being present
- High logical reads on filtered queries
- Key lookups that offset the benefits of using the index
- Slow filters on columns with repeated values
- Execution plans that ignore newly created indexes
- Dashboards or reports that slow down as the table size grows
These signs should prompt a closer look at query patterns, data distribution, and whether the index design still fits actual usage.
Additionally, a few mistakes usually come up over and over again when indexes are added without a clear evaluation process. These mistakes include:
- Assuming every indexed column will improve performance
- Confusing cardinality with selectivity
- Indexing boolean or status columns without checking how queries use them
- Ignoring data distribution and skew
- Overlooking column order in composite indexes
- Adding indexes to speed up reads without accounting for write overhead
- Keeping unused indexes on the assumption that they aren’t causing harm
- Assuming that high cardinality equates to high performance value
- Neglecting statistics maintenance
What index selectivity means for long-term indexing performance
When indexes are added without accounting for selectivity, the performance impact may not show up immediately, but it will surface as the data grows, query patterns shift, and maintenance overhead accumulates. Ultimately, index selectivity determines whether an index can keep delivering value over time or silently become a burden. To ensure they hold up as conditions change, administrators must continuously evaluate selectivity through execution plans, statistics, data distribution, and real query behavior, ensuring that every index in place is earning its role in long-term indexing performance.
Related topics:
