Index Selectivity

Selectivity = how well an index narrows down the rows it returns.

• High selectivity → index returns very FEW rows → very useful
• Low selectivity → index returns MANY rows → nearly useless

The Formula

Selectivity = Unique Values / Total Rows

Range: 0 to 1
→ closer to 1 = high selectivity = good index
→ closer to 0 = low selectivity = bad index

Example (1,000,000 rows):

email column   → 1,000,000 unique → Selectivity = 1.0 (perfect)
gender column  → 3 unique         → Selectivity = 0.000003 (terrible)
country        → 195 unique       → Selectivity = 0.000195 (poor)

Why Low Selectivity Index is Useless

For WHERE gender = 'M' — index points to ~500,000 rows (half the table). DB must perform 500,000 random disk reads. Sequential full table scan is FASTER. DB query planner sees this and IGNORES your index entirely.

Cardinality vs Selectivity

• Cardinality = raw count of unique values
• Selectivity = cardinality / total rows

Same cardinality can have different selectivity depending on table size. country: 195 unique — in 1,000 row table = 0.195 (acceptable); in 1,000,000 row table = 0.000195 (terrible).

Composite Index Selectivity

Individual: country = 0.000195 (poor), city = 0.001 (poor)
Combined:   (country, city) → much higher selectivity
"India, Mumbai" → far fewer rows than just "India"

Selectivity Threshold

General rule of thumb: Selectivity > 0.1 (index returns <10% of rows) → Index is worth using. Selectivity < 0.1 → Full scan might be faster.

How to Check in MySQL

SELECT
  COUNT(DISTINCT email) / COUNT(*) AS email_selectivity,
  COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity
FROM users;