Index selectivity measures how unique the values in an indexed column are. Higher selectivity means the column contains many distinct values, which helps MySQL filter rows more efficiently.

What Is Index Selectivity?

1
Selectivity = (Number of distinct values) / (Total number of rows)
2
A selectivity value close to 1.0 means high uniqueness (very selective).
3
A lower value means many duplicates (poor selectivity).

MySQL prefers indexes with high selectivity because they reduce the number of rows scanned during query execution.

Examples of Selectivity

1
High selectivity: email, user_id, phone_number
2
Medium selectivity: last_name, category_id
3
Low selectivity: gender, status flags, boolean fields

Impact on Performance

1
High-selectivity indexes dramatically speed up WHERE lookups.
2
Low-selectivity indexes are often ignored by the optimizer because full table scans may be faster.
3
Composite index performance depends on the selectivity of the leftmost column.
4
Low-selectivity columns (like boolean flags) should rarely be indexed alone.

Example

1
Table has 1,000,000 rows.
2
Column gender has only 2 values (M/F).
3
Selectivity = 2 / 1,000,000 = 0.000002 (very low).
4
MySQL will likely skip the index and perform a full table scan.

In summary, index selectivity is critical for index usefulness. High-selectivity indexes improve performance significantly, while low-selectivity indexes may be ignored because they do not filter rows effectively.