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

Question

Hi

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

I doubt that these fields are used in joins as they take 0 or 1 as input.

The primary use of an index in this case would be the ability to retrieve the data directly from the index, but as the index would be quite large the overhead would most likely make this ineffecient.

However the only valid approach here is to try on your dataset, as the data you are using can have a significant impact on the outcome.

Answer 2

I don't think you should add indexes to these fields because they do not contain different and much data. However as for type of fields, you may consider enum type.

Answer 3

Deciding whether to index or not should not be dependent on the data type, but on

• how often you will search for the field
• how many records the table will have (order of magnitude)
• whether waiting for the non-indexed query (full table scan) is acceptable for the user

Answer 4

My database guru says, "never add any index if you do not know the query". In your case, looks like you do know the query. So yes, we can think about index. In case you have a data warehouse type of system, create bitmap index. otherwise do not create any index. B-tree indices are bad on such a small cardinality columns.

Answer 5

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).