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

Posted by Geert-Jan on Stack Overflow See other posts from Stack Overflow or by Geert-Jan
Published on 2010-06-10T19:04:22Z Indexed on 2010/06/11 2:42 UTC
Read the original article Hit count: 334

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

© Stack Overflow or respective owner

Related posts about mysql

Related posts about bit-manipulation