Course contentsShow
← Data Science
Lesson 1083 of 2,14527. Database Design and OptimizationFree lesson

Index Selectivity and Cardinality

Understand how unique values and data distribution affect index effectiveness and the optimizer's choices.

Index Selectivity and Cardinality

You'll learn how the distribution and uniqueness of data in your indexed columns directly influence whether the database optimizer actually uses those indexes.

The Core Idea

Not all indexes are created equal. The database optimizer decides whether to use an index based on two key metrics:

Cardinality is the count of unique values in a column. A column with 1,000,000 rows but only two unique values (like a boolean is_active field) has low cardinality. A user_id column with 1,000,000 unique values has high cardinality.

Selectivity is how well a query condition narrows down the result set. It's calculated as unique_values / total_rows. High selectivity (closer to 1.0) means the index can filter aggressively. Low selectivity (closer to 0) means most rows match anyway.

Why This Matters

Imagine you're searching a phone book for "people named Smith" versus "people named Zachariah Xenophon III." The second search (high selectivity) benefits enormously from alphabetical ordering. The first (low selectivity) barely helps—you'll still flip through dozens of pages.

The optimizer makes similar calculations. Indexing a gender column (low cardinality: ~2 values) rarely helps because scanning half your table is often faster than jumping through index pages. But indexing email (high cardinality) is extremely effective because each lookup returns just one row.

When selectivity is poor (a query matches 30%+ of rows), the optimizer may ignore your index entirely and do a full table scan—it's genuinely faster.

Key Takeaway: Indexes work best on high-cardinality columns used in selective queries; the optimizer intelligently skips indexes when scanning the table directly would be more efficient.