开发者

MySQL doesn't use indexes when query over BIT field using bitwise functions

开发者 https://www.devze.com 2023-01-13 12:44 出处:网络
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:

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?

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号