6

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.

Mohamed Ikal Al-Jabir
  • 89
  • 2
  • 6

4 Answers4

13

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
  • 40,729
  • 5
  • 57
  • 110
  • 4
    +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 Jun 15 '10 at 04:31
  • 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 Jun 15 '10 at 06:07
1

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.

Community
  • 1
  • 1
Jan K.
  • 1,607
  • 2
  • 13
  • 22
1

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
  • 24,650
  • 8
  • 50
  • 93
  • 2
    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 Jun 15 '10 at 04:34
1

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
  • 23,890
  • 5
  • 56
  • 43