What Are Index Selectivity and Cardinality?
Learn the difference between cardinality and selectivity, and how they determine whether an index actually speeds up a query.
Expected Interview Answer
Cardinality is the number of distinct values in a column, and selectivity is the ratio of distinct values to total rows — a high-selectivity column (close to 1, meaning most values are unique) lets an index eliminate most rows quickly, while a low-selectivity column (close to 0, meaning few distinct values) gives the index very little filtering power.
The query optimizer uses selectivity to decide whether using an index is actually cheaper than a full table scan: if a WHERE clause on a low-selectivity column like a boolean or a status flag with only three states still matches a large fraction of rows, walking the index and then fetching each matching row can cost more than simply scanning the table sequentially. High-selectivity columns, like a unique email or an order ID, let an index seek eliminate nearly all rows in one step, making the index enormously valuable. This is why interviewers expect candidates to reason about which columns are worth indexing based on the actual distribution of their values, not just intuition about "important" columns.
- Guides which columns are worth indexing at all
- Explains why the optimizer sometimes skips an available index
- Informs column order in composite indexes (most selective often first)
- Prevents wasted index maintenance cost on low-value columns
AI Mentor Explanation
A player’s unique jersey number has high cardinality and high selectivity — knowing the number narrows a search to exactly one player out of thousands, so an index on jersey number is extremely useful. A player’s "batting hand" (left or right) has low cardinality — only two values — so an index on it barely narrows anything, since roughly half of all players share each value; a scout might as well scan the full roster instead of using that index. Selectivity is precisely this ratio of how much a value narrows the search.
Step-by-Step Explanation
Step 1
Measure cardinality
Count the number of distinct values in the candidate column relative to total row count.
Step 2
Compute selectivity
Divide distinct value count by total rows; values near 1 are highly selective, near 0 are not.
Step 3
Let the optimizer estimate cost
The planner uses column statistics to estimate how many rows a predicate matches before choosing index seek vs. table scan.
Step 4
Design indexes accordingly
Prioritize indexing high-selectivity columns, and order composite index columns from most to least selective when it aids filtering.
What Interviewer Expects
- Correct definitions distinguishing cardinality (count) from selectivity (ratio)
- Understanding that low-selectivity indexes may be skipped by the optimizer
- A concrete example contrasting a unique column with a low-cardinality column
- Awareness of how selectivity informs composite index column ordering
Common Mistakes
- Using cardinality and selectivity interchangeably without defining either
- Assuming any indexed column is automatically used by the query planner
- Ignoring that stale statistics can cause the optimizer to misjudge selectivity
- Not connecting selectivity to composite index column ordering decisions
Best Answer (HR Friendly)
“Cardinality is how many distinct values a column has, and selectivity is what fraction of the table a typical value narrows down to. A column like a unique ID is highly selective and makes a great index, while a column like a status flag with only a few values is not very selective, so an index on it might not even get used by the database.”
Code Example
-- Cardinality: count of distinct values
SELECT COUNT(DISTINCT status) AS distinct_statuses,
COUNT(*) AS total_rows
FROM Orders;
-- Selectivity: distinct values / total rows
-- e.g. 3 statuses / 1,000,000 rows = 0.000003 (very low selectivity)
-- vs. order_id: 1,000,000 / 1,000,000 = 1.0 (maximum selectivity)
-- A highly selective column is an excellent index candidate
CREATE INDEX idx_orders_id ON Orders (order_id);
-- The optimizer may ignore an index on a low-selectivity column
-- and choose a full table scan instead
SELECT * FROM Orders WHERE status = 'pending';Follow-up Questions
- How does the query optimizer use column statistics to estimate selectivity?
- Why might an index on a low-selectivity column still be created deliberately?
- How does selectivity influence the column order in a composite index?
- What happens when table statistics become stale relative to actual data?
MCQ Practice
1. What does selectivity measure?
Selectivity is distinct values divided by total rows; values closer to 1 mean each value narrows the result set sharply.
2. A column with only two possible values across a million rows is best described as?
Only two distinct values means low cardinality, and each value matches roughly half the rows, meaning low selectivity.
3. Why might a query optimizer skip an available index on a low-selectivity column?
If a predicate matches a large share of the table, the extra cost of index traversal plus row lookups can exceed a straight sequential scan.
Flash Cards
What is cardinality? — The number of distinct values present in a column.
What is selectivity? — The ratio of distinct values to total rows; higher means more filtering power per value.
Why might a low-selectivity index go unused? — Because matching rows still make up a large share of the table, making a table scan cheaper than index-plus-lookup.
How does selectivity affect composite indexes? — More selective columns are often placed earlier so the index narrows the search as quickly as possible.