is mysql index useful on column 'state' when only doing bit-operations on the column?
- by Geert-Jan
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