Intermediate LevelSQL & MySQL
What is Index Selectivity and how do you calculate Cardinality to optimize index ordering?
❓ Interview Prompt: Explain Index Selectivity ($S = U / N$), column cardinality, picking high-selectivity columns for composite indexes, and query performance.
Index Selectivity measures the uniqueness of values stored in a column:
Formula:
$$\text{Selectivity} = \frac{\text{Number of Unique Values } (U)}{\text{Total Table Rows } (N)}$$
•
High Selectivity ($\sim 1.0$): Primary Keys, UUIDs, Emails. B-Tree index lookups pinpoint exact rows instantly.
•
Low Selectivity ($\sim 0.001$): Gender, Boolean flags. Index seeks are ineffective, causing full table scans.
•
Rule: Place high-selectivity columns first in composite index definitions!
Code Implementation Example
sql
-- Calculating column selectivity to guide index design
SELECT
COUNT(DISTINCT email) / COUNT(*) AS email_selectivity, -- High (~1.0)
COUNT(DISTINCT status) / COUNT(*) AS status_selectivity -- Low (~0.001)
FROM users;💡Calculating column selectivity ratios to optimize composite index design.
🎯 Interview Key Takeaway
Place high-selectivity columns first in composite indexes to maximize index filtering efficiency.
#SQL#Selectivity#Cardinality#Indexes#Intermediate
Back to all SQL & MySQL Questions→