is mysql index useful on column 'state' when only doing bit-operations on the column?

Question

I have a lot of domain entities (stored in mysql) which undergo lots of different operations. Each operation is executed from a different program. I need to keep (flow)-state for these entities which I implemented in as a long field 'flowstate' used as a bitset.

to query mysql for entities which have undergone a certain operation I do something like:

select * from entities where state >> 7 & 1 = 1

Indicating bit 7 (cooresponding to operation 7) has run. (<-- simplified)

Anyway, I really didn't pay attention to the performance implications of this setup in the beginning, and I think I'm in a bit of trouble since queries as the above run pretty slow.

What I'd like to know: Does an mysql index on 'flowstate' help at all? After all it's not a single value Mysql can quickly find using a binary sort or whatever.

If it doesn't, are there any other things I could do to speed things up? . Are there special 'mask-indices' for fields with use-cases as the above?

TIA, Geert-jan

Answer 1

I don't think there's any way for MySQL to use an index in that case. If you knew that bits always got set left to right, or right to left, or in some specific order, you may be able to do some tricks where you limit the number of rows that need to be checked:

... where state < ? and state > ? and ...

If that were the case, then an index on state would be useful. That's probably a long shot, though. Otherwise your best bet is probably to revise your schema.

Answer 2

Use EXPLAIN on the statement to see how it's accessing the table. It's worth putting an index on the field and rerunning EXPLAIN to see if MySQL uses it. There should be an apparent performance difference between the results if the table is fairly large.