Course contentsShow
← Software Engineering
Lesson 875 of 1,93327. Database Design in PracticeFree lesson

Index Selectivity and Cardinality

Evaluating index effectiveness based on the uniqueness of values and query filtering power.

Index Selectivity and Cardinality

You'll learn how to evaluate whether an index will actually help your queries by understanding the uniqueness of its values and how much data it filters out.

What These Terms Mean

Cardinality refers to the number of distinct values in a column. A column with many unique values has high cardinality; one with few unique values has low cardinality.

Selectivity measures how well an index narrows down results. It's calculated as:

Selectivity = (Unique Values) / (Total Rows)

High selectivity (close to 1.0) means most values are unique. Low selectivity (close to 0) means many rows share the same values.

Why This Matters

An index on a high-cardinality, high-selectivity column is usually effective because it quickly filters out most rows. Think of a user_id or email column—each lookup finds very few rows.

An index on a low-cardinality column like is_active (only TRUE/FALSE) or status (maybe 5-10 values) often performs poorly. The database might scan half your table anyway, making the index overhead not worth it.

Analogy: Imagine organizing books. Indexing by ISBN (high cardinality) lets you find one specific book instantly. Indexing by "fiction vs non-fiction" (low cardinality) doesn't help much—you still face a huge pile to search through.

Practical Guidelines

  • Index high-cardinality columns used in WHERE clauses, especially for exact matches
  • Avoid indexing very low-cardinality columns unless combined with other columns in a composite index
  • Check actual query patterns—even low-cardinality indexes can help if queries filter to very small result sets
  • Remember that cardinality can change as data grows

Key Takeaway: Indexes work best on columns with high cardinality and selectivity, where each lookup filters out most rows; low-cardinality columns rarely benefit from standalone indexes.