MySQL does not use indexes when querying a BIT field using bitwise functions

Question

I have a field of type BIT in my MySQL table. I want to save record statuses using a bit value, for example:

1 = status1 2 = status2 4 = status3 8 = status4 

Each record can have several statuses at once. For status1 and status3, the value will be 1 + 4 = 5. I can query the table for all entries with status 3 using:

 SELECT * FROM `table` WHERE `statuses` & 4 

I have an index on statuses , but EXPLAIN indicates that the index is not used. Is it possible to use the index in such a situation?

PS Using a separate many-to-many link table is a more normal solution, but I would like to have a more “flat” structure for this.

Answer 1

It will be difficult for the optimizer to use an index in a bit field. Consider all the different values ​​that bit 2 has (a value of "4"): 4, 5, 6, 7, 12, 13, 14, 15, 20, 21, 22, 23, ... How will the optimizer use this effectively?