What is index selectivity and cardinality, and why do they matter?

Question

Accepted Answer

58
   What is index selectivity and cardinality, and why do they matter?
  Level: Advanced — A senior-level concept that explains WHY some indexes help and others don't.

  Both terms describe how "unique" the values in a column are, which directly determines how effective an index will be.

  
     Cardinality: The number of distinct (unique) values in a column. High cardinality = many unique values (e.g., email); low cardinality = few unique values (e.g., gender).
     Selectivity: The ratio of distinct values to total rows. It measures how well an index narrows down the search.
  

   The Selectivity Formula
  Selectivity is calculated as:
  $$ Selectivity = \frac{	ext{Number of Distinct Values}}{	ext{Total Number of Rows}} $$
  The result ranges from just above 0 (very poor) to 1 (perfect, all unique). A value close to 1 means an excellent index candidate.

  
     Why it matters: The MySQL optimizer uses cardinality to decide whether to use an index or do a full scan. On a low-selectivity column (like gender with 2 values), an index barely narrows results, so MySQL often ignores it.
  

   Selectivity Examples (10,000-row table)
  
    
      
        
          Column
          Distinct Values
          Selectivity
          Index Quality
        
      
      
        
          email
          10,000
          1.0
          Excellent
        
        
          last_name
          3,000
          0.30
          Good
        
        
          city
          50
          0.005
          Weak
        
        
          gender
          2
          0.0002
          Poor
        
      
    
  

   Practical Takeaways
  
     Index high-selectivity columns (email, phone, user_id) for the biggest gains.
     Avoid indexing low-selectivity columns alone (gender, boolean flags, status).
     In composite indexes, place higher-selectivity columns first.
  

   Quick Example
-- Check cardinality of each index (see the Cardinality column)
SHOW INDEX FROM employees;

-- Count distinct values to estimate selectivity manually
SELECT
    COUNT(DISTINCT email)  AS email_distinct,
    COUNT(DISTINCT gender) AS gender_distinct,
    COUNT(*)               AS total_rows
FROM employees;

-- Refresh cardinality statistics so the optimizer chooses wisely
ANALYZE TABLE employees;

-- High selectivity -> index is used
EXPLAIN SELECT * FROM employees WHERE email = 'a@x.com';   -- uses index

-- Low selectivity -> optimizer may skip the index
EXPLAIN SELECT * FROM employees WHERE gender = 'M';        -- may full scan

  
     Interviewer tip: The one-liner they want — "Cardinality is the number of distinct values in a column; selectivity is that count divided by total rows. High selectivity makes an excellent index, while low-selectivity columns (like gender) are poor index candidates."

Column	Distinct Values	Selectivity	Index Quality
email	10,000	1.0	Excellent
last_name	3,000	0.30	Good
city	50	0.005	Weak
gender	2	0.0002	Poor

What is index selectivity and cardinality, and why do they matter?

Answer with Explanation

What is index selectivity and cardinality, and why do they matter?

The Selectivity Formula

Selectivity Examples (10,000-row table)

Practical Takeaways

Quick Example