View source for Index selectivity

'''Index selectivity''' is the ratio of distinct values to total rows in an indexed column, measuring the index's ability to narrow a query result set. A perfectly selective index — such as a primary key — eliminates all but one row per lookup. A poorly selective index — such as a boolean flag or a gender column — may eliminate only half the rows, leaving the database engine to scan a large fraction of the table anyway. In such cases, the index is not merely useless; it can be actively harmful, because the overhead of random I/O through the index exceeds the cost of a sequential table scan.

Selectivity is a structural property of the data distribution, not of the index itself. A column that is highly selective today may become non-selective tomorrow as the data grows or the distribution shifts. The [[Query optimization|query optimizer]] uses [[Cardinality estimation|cardinality estimation]] and [[Histogram statistics|histogram statistics]] to decide whether to use an index, but these estimates are always provisional. The index selectivity problem is therefore not a static design choice but a temporal one: the system must continuously re-evaluate whether its indexes still match the data and the workload.

[[Category:Computer Science]] [[Category:Systems]]