views:

95

answers:

4

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.

+1  A: 

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.

Jan Kuboschek
I was thinking similarly, since the conditions of bools are already about as atomic as you can get.
Mohamed Ikal Al-Jabir
Your indexes will really depend on your query. If you post that, I'm sure we can help. You may also want to run the EXPLAIN statement in your database engine.
Jan Kuboschek
in MySql, EXPLAIN is your best friend
ceteras
Selectivity is what's important, not the number of possibilities: if only 1 in 1000 rows has `FOO=TRUE`, it may well be worth indexing. +0.
j_random_hacker
+8  A: 

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.

Mark Wilkins
+1. A lot of the answers here seem to be missing the fact that it's the selectivity that counts, not the number of possible values.
j_random_hacker
It still gives quite a bit of overhead because it has to store the value for each row in the index, not just the ones it'll match on. This is why a partial index is often a much better choice.
Magnus Hagander
+1  A: 

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.

egrunin
Selectivity is what's important, not the number of possibilities: if only 1 in 1000 rows has `FOO=TRUE`, it may well be worth indexing. +0.
j_random_hacker
+1  A: 

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.

Magnus Hagander