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

Question

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.

The presence of an index does not mean the optimizer will choose to use it. — OMG Ponies
– OMG Ponies, Commented Aug 24, 2010 at 19:59
You might find this thread useful: MySQL Forums :: Performance :: are index scans possible with bitwise comparison — Mike
– Mike, Commented Aug 24, 2010 at 20:03
I will have a lot of values in the table, I need to be sure that I will not have problems when table grows, any tips how to check? — artvolk
– artvolk, Commented Aug 24, 2010 at 20:05
Is there really a reason to do this instead of just having 4 bit columns? This type of programming may be quite elegant and quick, but it requires a lot more training and documentation when you hand it off to someone else. — Bill
– Bill, Commented Aug 24, 2010 at 20:10
Bill, The number of statuses is not fixed (but maximum for bitwise operations in 64 is enough for me), so I need something not depending on adding new columns. — artvolk
– artvolk, Commented Aug 24, 2010 at 20:41

wallyk · Accepted Answer · 2010-08-24 20:11:49Z

6

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?

answered Aug 24, 2010 at 20:11

wallyk

58.1k17 gold badges92 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wallyk Over a year ago

Not that I've been able to figure out. The most general solution is to break the bits out into separate fields. If it can't be done in the original table, then creating a new table containing only the split bit fields would surely help.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related