Does it make sense to index data with a small number of unique values?

In general, indexing data with a small number of unique values (low cardinality) is less efficient than indexing data with high cardinality. The reason is that a low cardinality index contains many repeated values, and scanning such an index can lead to reading a large number of rows that match the same value. The query optimizer may prefer a full table scan if it is faster than scanning the index followed by accessing the table data.

However, there are cases where indexing low cardinality data can be justified:

• Frequent queries with predicates on this field: If a low cardinality field is frequently used in a WHERE condition, even with a small number of unique values, an index can speed up searches.
• Using the index in a multi-column index: A low cardinality field can be part of a composite index. In this case, it can help filter data based on other high-cardinality fields.
• Using a cover index: If a query can be fully satisfied by data from the index (i.e., all necessary columns are included in the index), then even a low cardinality indexed field can be acceptable, as no access to the table itself is needed.
• Using for sorting (ORDER BY): An index on a low cardinality field can be used to speed up sorting results if the sorting is performed on this field.

Modern query optimizers are quite intelligent and can decide whether to use an index or a full scan based on data statistics and operation costs. Therefore, even if an index is created on a low cardinality field, the optimizer may decide not to use it if it does not speed up the query.

The best approach is to analyze specific queries and data, as well as test performance with and without the index, to make an informed decision.