Selectivity

Hook: Interviewers love selectivity because it explains why the same index can be blazing fast for one query and useless for another.

Question: What does selectivity mean in SQL, especially for indexes and performance?

Answer: Selectivity is the fraction of rows that match a filter. High selectivity means only a small part of the table matches, so an index is usually helpful; low selectivity means many rows match, so a full scan may be cheaper. The optimizer uses table statistics to estimate this before it picks a plan.

Interview-Ready Answer: In SQL, selectivity means how many rows a predicate keeps. I think of it as how picky the WHERE clause is. A filter like email = 'a@x.com' is highly selective because it usually finds one row, so an index is a great fit. A filter like status = 'open' is often low selectivity because it may match a big chunk of the table, so the optimizer may prefer a sequential scan or a different index strategy.

What selectivity means

Selectivity is the fraction of rows that survive a filter. If a table has 1,000,000 rows and 1,000 match, the selectivity is 0.1%. In planner terms, this is part of cardinality estimation, which means the database's guess about how many rows a step will output.

How the optimizer uses it under the hood

1. The query arrives with a predicate, like WHERE customer_email = 'amy@example.com' or WHERE status = 'open'.
2. The optimizer reads statistics: total row count, distinct values, most common values, and sometimes histograms. A histogram is just a bucketed summary of data distribution.
3. It estimates how many rows match each predicate. A highly selective filter might be estimated at 1 row or 0.1% of the table.
4. It compares access paths: a sequential scan, an index scan, or a combined strategy. A sequential scan means reading the table in order from start to finish.
5. It picks the cheapest plan based on estimated page reads, CPU work, and row lookups.
6. At execution time, the engine follows that plan. If the estimate was wrong, the chosen plan may be slower than expected.

High selectivity vs low selectivity

Why it matters for indexes

A B-tree index is a sorted structure. It is very good when the query can jump to a tiny slice of the data, then read only a few matching rows. The tree walk itself is roughly O(log n), but the real cost includes fetching the matching rows. That is why selectivity matters more than the word index alone.

For a table with 10 million rows, a lookup that returns one row may touch only a few index pages plus one table row. A lookup that returns 3 million rows can become expensive because the engine must visit many rows, and random I/O can dominate. In that case, a sequential scan may win because it reads pages in order. There is no universal cutoff like always use an index below 10%; the right answer depends on table size, cache warmth, row clustering, and whether the query is covered by the index.

Important edge cases

• Low-cardinality columns like status or is_active often have low selectivity, so a plain index on them may not help much.
• Functions on columns like LOWER(email) can hide the normal index because the engine cannot use the raw stored values directly.
• Composite indexes depend on column order. The most selective leading column usually matters most for search.
• Stale statistics can fool the optimizer into thinking a filter is more selective than it really is, which leads to a bad plan.
• Small tables often scan faster than they index because the overhead of using the index is not worth it.

Memory rule: ask yourself whether the query is looking for one needle or a whole haystack. One needle favors an index; a haystack often does not.

Real-World Example: In an e-commerce checkout service, the team had an orders table with millions of rows. A dashboard query filtered by status = 'PAID' and expected to be fast because there was an index on status. The problem was that most recent orders were already paid, so the predicate was low selectivity. The database had to bounce through lots of matching index entries and then fetch many table rows, which made p95 latency jump from tens of milliseconds to nearly a second.

The outage symptoms were easy to miss at first: CPU stayed moderate, but disk reads spiked, slow query logs showed many rows examined, and support agents saw the dashboard timing out during peak traffic. The fix was to use a more selective access pattern: a composite index for the common dashboard filter, plus a summary table for reporting. The lesson was simple: an index is not magic; it only helps when the filter narrows the data enough.

Follow-up & Tricky Questions:

• How do you tell if a column is selective enough? Check the data distribution and the query plan. If a filter returns only a tiny fraction of rows, an index is more likely to help; if it returns a large chunk, a scan may be better.
• How does selectivity affect join order? The optimizer often wants the most selective filter early so it shrinks intermediate results. Smaller inputs make joins cheaper.
• Can a low-selectivity index still help? Yes, if it is covering, part of a composite index, or used with another predicate. It can also help when the engine combines multiple indexes.
• What happens when statistics are stale? The optimizer can misestimate selectivity and choose the wrong plan. That is why statistics updates matter after big data changes.
• Why might two similar queries choose different plans? Small changes in constants can change estimated selectivity, which can push the optimizer from an index scan to a sequential scan or the other way around.
• Is a unique index always fast? It is usually very good for lookups, but the write cost is still real. Every insert, update, or delete must maintain the index too.
• Does WHERE LOWER(col) = ... use the normal index? Usually not. The function changes the value being searched, so you often need an expression index or normalized data.

• Tricky: Is selectivity the same as cardinality? No. Selectivity is the fraction that matches; cardinality often means the number of rows returned or, in other contexts, the number of distinct values. Candidates often mix those up.
• Tricky: If an index exists, will the database always use it? No. The optimizer may still pick a sequential scan if too many rows match or if table statistics say the index is not worth it.
• Tricky: Is a boolean column a good index target? Usually not by itself, because it has very low selectivity. It can still help if combined with another column or if one value is rare.

Common Mistakes:

• Confusing selectivity with distinct values. Correction: selectivity is about how many rows match a filter, not just how many different values exist.
• Assuming every index is helpful. Correction: if a predicate matches too many rows, the index can be slower than a sequential scan.
• Ignoring data skew. Correction: one value may be very common and another very rare, so average stats can hide the real cost.
• Forgetting column order in composite indexes. Correction: the leading column matters a lot for searchability and selectivity.

Memory Hook: Think of a phonebook: if you want one person's name, the index is perfect; if you want half the city, just read the list.

Cheat Sheet:

• Selectivity = matched rows / total rows.
• High selectivity means few rows match; indexes usually shine.
• Low selectivity means many rows match; scans often win.
• The optimizer uses statistics, not guesswork alone.
• Functions on columns can block normal index use.
• Composite, covering, and partial indexes can improve borderline cases.

Practice Tasks:

• Create a table with one unique column and one repeated column, then compare which predicate is more selective.
• Add an index to the repeated column and test whether the plan changes for small vs large result sets.
• Rewrite a function-wrapped filter so it can use an index, or create an expression index if your database supports it.