Using boolean or enum columns in indices?

Question

I`ve read that columns that are chosen for indices should discriminate well among the rows, i.e. index columns should not contain a large number of rows with the same value. This would suggest that booleans or an enum such as gender would be a bad choice for an index.

But say I want to find users by gender and in my particular database, only 2% of the users are female, then in that case it seems like the gender column would be a useful index when getting the female users, but not when getting all the male users.

So would it generally be a good idea to put an index on such a column?

Answer 1

Indexing a low-cardinality column to improve search performance is common in my world. Oracle supports a "bitmapped index" which is designed for these situations. See this article for a short overview.

Most of my experience is with Oracle, but I assume that other RDBMS' support something similar.