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: 328

Filed under:

mysql

|

bit-manipulation

|

bitwise-operators

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

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about bit-manipulation

Find three numbers appeared only once using bit manipulation

as seen on Stack Overflow - Search for 'Stack Overflow'
In a sequence of length n, where n=2k+3, that is there are k unique numbers appeared twice, how to find the three unique numbers that appeared only once using bit manipulation? for example, in sequence 1 1 2 6 3 6 5 7 7 the three unique numbers are 2 3 5. >>> More
Learning C bit manipulation

as seen on Stack Overflow - Search for 'Stack Overflow'
I didn't know any better name for a title of the question. For ex: on one of my previous questions one answered ((a)-(b))&0x80000000) 31 - this is kind of a too advanced for me and i can't really get what it means. I am not looking just for an answer of that, but i need someone to tell me some… >>> More
Bit manipulation, permutate bits

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi. I am trying to make a loop that loops through all different integers where exactly 10 of the last 40 bits are set high, the rest set low. The reason is that I have a map with 40 different values, and I want to sum all different ways ten of these values can be multiplied. (This is just out of… >>> More
bit manipulation in java

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a fragment of bytes in a byte[]. The total size of the array is 4 and I want to convert this into a positive long number. For example if the byte array is having four bytes 101, 110, 10, 1 then i want to get the long number represented by binary sequence 00000000 00000000 00000000 00000000… >>> More
Doing 64 bit manipulation using 32 bit data in Fixed point arithmetic using C.

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I am stuck with a problem. I am working on a hardware which only does support 32 bit operations. sizeof(int64_t) is 4. Sizeof(int) is 4. and I am porting an application which assumes size of int64_t to be 8 bytes. The problem is it has this macro BIG_MULL(a,b) ( (int64_t)(a) *… >>> More