I have a field of type BIT in my MySQL table. I want to store statuses of the record using bit value, for example:
1 = status1
2 = status2
4 = status3
8 = status4
Each record can have many statuses at once. For status1 and status3 the value will be 1 + 4 = 5. I can query table for all records with status3 using:
SELECT *
FROM `table`
WHERE `statuses` & 4
I have index on statuses, but EXPLAIN tells that no index is used. Can I use index in such situation?
P.S. Using separate many-to-many linking table is more normalized solution, but I'd like to have more 'flat' structure for this.
解决方案
It would be difficult for the optimizer to make use of an index on a bitfield. Consider all the different values which have bit 2 (value "4") set: 4, 5, 6, 7, 12, 13, 14, 15, 20, 21, 22, 23, ... How would the optimizer make efficient use of that?