Should I create indexes on tinyint field types in mysql tables?

Question

I was just working on a web application and find that the most the mysql tables have fields, like, is_live, can_do, required, published (and many more) having field type TINYINT, and takes either 0 or 1 only. I was just wondering if I need to create indexes on these columns as scripts are using joins which include these columns as well. So questions are :

Should I add indexes to these columns as well?

Should I change the type to anything else?

Please see this question is more to do with understanding the concept rather than solving a problem.

Thanks.

Answer 1

The general advice is that an index on boolean field is rarely going to be useful.

B-tree indexes are most effective for high-cardinality data (i.e. columns with many possible values, where the data in the column is unique or almost unique).

Some database engines, like Oracle and Postgres, support Bitmap Indexes. Bitmap indexes have traditionally been considered to work well for data such as gender (Male or Female), which has a small number of distinct values, but with many occurrences of those values.

MySQL does not currently support bitmap indexes, but may achieve similar functionality using its "index_merge" feature. Bitmap indexes should be introduced with the Falcon engine (Source).