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

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

Should I put an index on 开发者_Python百科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.

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.

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.

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.

You'd usually not want to index on a boolean at least. However, creating a partial index over something else with the boolean check as a predicate can be one of the most efficient indexing options available - if your boolean field cuts out a lot of data from the index. This is often a lot more efficient than doing a combined index with another column.