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

Question

An old mentor once told me to place indexes on most things for which the WHERE is used.

Should I put an index in BOOL/TINYINT ? In the table in which I work, a significant part of them, and often the results are filtered anywhere from 1-20 of these Boolean conditions.

Answer 1

There is one situation in which an index in a Boolean field (or another low power field) may be useful. If relatively one of the values ​​(for example, 10 TRUE values ​​out of a million) is relatively small, and you often look for these few values, then the index will be useful.

Answer 2

I can’t talk about tiny ints (it can be very much the same thing), but I would not index Booleans for the simple reason that they can only take two values.

As far as I remember, you want to use indexes in high power columns. What is the point of having an index for a column that can only take two different values? This is a waste of space without real benefits.

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

As already noted, you may want to put the index in a set of conditions; which of them depend on your request.

Answer 3

You can index a combination of fields.

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

Answer 4

Typically, you do not want to index by a boolean value. However, creating a partial index over something else with a logical check as a predicate may be one of the most effective indexing options - if your logical field cuts a large amount of data from the index. This is often much more efficient than a combined index with another column.