Selectivity

How well a column narrows down the search

Selectivity measures the fraction of rows that a given indexed value distinguishes — in other words, how effectively filtering on that column narrows down the result set. It is typically expressed as:

selectivity = number of distinct values / total number of rows

A selectivity close to 1 (high selectivity) means most values are unique or nearly unique — filtering on that column narrows the search down to very few rows. A selectivity close to 0 (low selectivity) means there are only a handful of distinct values shared by many rows — filtering on that column barely narrows anything down.

Concrete example

In our 1,000,000-row students table:

• email: essentially every student has a unique email → close to 1,000,000 distinct values → selectivity ≈ 1,000,000 / 1,000,000 = 1.0 — very high selectivity. An index on email is excellent, since WHERE email = '...' narrows the search from a million rows down to essentially one.
• is_active (a boolean flag): only 2 possible values (true/false) → selectivity ≈ 2 / 1,000,000 = 0.000002 — extremely low selectivity. An index on is_active barely helps: WHERE is_active = true might still match 700,000 rows out of 1,000,000 — the index would need to return most of the table anyway, so the engine may as well just scan the table directly.
• department (say, 20 possible departments): selectivity ≈ 20 / 1,000,000 = 0.00002 — still low, though each department's ~50,000 rows is a real improvement over scanning all 1,000,000; not as dramatic as email but potentially still useful depending on the query.

Why this matters for index design

A high-selectivity column is an excellent index candidate: the index does real work, narrowing a huge table down to a tiny handful of matching rows. A low-selectivity column makes a poor index candidate, because even after using the index, the engine still has to retrieve (and likely bookmark-lookup, 20.6) a large fraction of the table — at that point, a plain full table scan can actually be cheaper than the index-plus-many-lookups approach, since sequential scanning avoids the scattered random-access I/O pattern of following many index pointers.

Edge cases

• Selectivity can vary across a skewed distribution: a department column might have 19 departments with ~1,000 students each and one department ("Computer Science") with 800,000 — the overall selectivity number can be misleading, since filtering to the huge department is low-value even though filtering to a small one is very high-value. Real query optimizers use more detailed statistics (histograms) beyond a single selectivity ratio to handle this.
• Selectivity is directly related to (and often computed from) cardinality (20.13, the next topic) — selectivity = cardinality / total rows.
• A composite index's effective selectivity is evaluated across the combination of its leading columns, not each column in isolation.

Interview-style Q&A

Q: What does high selectivity mean, and why is it good for indexing? A: High selectivity means a value narrows the result set down to very few matching rows (close to unique) — it's good because the index actually does meaningful work reducing the search space.

Q: Why is a boolean flag column a poor index candidate? A: Because it only has 2 distinct values, so filtering on it still leaves a huge fraction of the table matching — the index barely narrows anything down, giving low selectivity.

Key takeaway: selectivity quantifies "how much does filtering on this column actually help" — the higher, the more valuable an index on that column is.