I have a f开发者_JAVA技巧ield 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?
精彩评论