CODEWITHSUNDEEP
sqlmysqlbitwise-operators
artvolk
artvolk

Reputation: 9518

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

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.

Upvotes: 5

Views: 2443

Answers (1)

wallyk
wallyk

Reputation: 57784

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?

Upvotes: 6

Related Questions