CODEWITHSUNDEEP
database
deltanovember
deltanovember

Reputation: 44051

Is there a point indexing a database column that does not have many different values?

For example suppose I have a column TYPE which only takes a few values. I have queries such as

WHERE TYPE=...

Will indexing this column provide any improvement in performance?

Upvotes: 3

Views: 120

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300549

If a column's selectivity is very low (i.e. there are not many different values) then a query optimizer is unlikely to utilise an index on that column.

But, say you had a Active column (or other column with few distinct values), and the majority of records were active and just a few inactive (i.e. Active equal to false), then a query with criteria WHERE Active = False might use an index on the Active column. [For SQL Server, the cutoff point is approximately 10% of the rows]

There are also other heuristics at play in multiple column indexes (and depends on your RDBMS)

For instance: SQL Server Indexing: Using a Low-Selectivity BIT Column First Can Be the Best Strategy (Thanks, Damien)

SQL Server 2008 onwards, has the ability to create 'Filtered Indexes' for subsets of data:

When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

Upvotes: 4

Related Questions