Put indexes on a table with lots of BOOL/TINYINT?

Question

An old mentor once told me to place indexes on most things you use a WHERE clause for.

Should I put an index on BOOL/TINYINT's? There is a significant amount of them in the table I am working with, and often results are filtered by anywhere from 1-20 of these boolean conditions.

Answer 1

There is one situation in which an index on a boolean field (or other low cardinality field) might be useful. If there are relatively few of one of the values (e.g., 10 TRUE values out of a million) and you are searching for those few values fairly often, then an index would be useful.

Answer 2

You might index on a combination of fields.

Indexing on Bool1 might be pointless, because there's only 2 values. Indexing on Bool1, Bool2, Bool3, Bool4, Bool5...Bool16 has 2^16 values.

Answer 3

I can't speak on the tiny ints (it might very well be the same), but I would not index booleans for the simple reason that they can assume only two values.

As far as I remember, you want to use indexes on columns with a high cardinality. What's the point on having an index for a column that can assume only two different values? It's a waste of space with no real gain.

I also recommend What are some best practises and “rules of thumb” for creating database indexes? for further reading.

As some have already pointed out, you may want to consider putting an index on a collection of conditions; which ones depends on your query.