CODEWITHSUNDEEP
mysqlsqlindexingbit-manipulationwhere-clause
Daren
Daren

Reputation: 3407

Does indexed search applies in a where clause with a bitwise operation?

I have a table with an int that stores about 30 boolean flags. Let's build an example:

Table MyTable
idRow | idUser | idThing | flags | moreColumns

I have a query that runs very often scanning that table:

SELECT idRow, idThing,  morColumns FROM MyTable WHERE idUser = ? AND flags = ?

So an index is built with (idUser, flags) however due to an update in logic I know have to either increase the query with more flags combinations, or allow a bitwise operation that indicates the flag a truly want:

SELECT idRow, idThing,  morColumns FROM MyTable WHERE idUser = ? AND (flags = combination1 OR flags = combination2 OR flags = combination3) -- may have to keep increasing in the future

or the other option which I like better:

SELECT idRow, idThing,  morColumns FROM MyTable WHERE (PRIVILEGE_FLAGS & flag) = flag;

But I am unsure as to whether the index created will help with the query or it is better to use the other option.

Thank you.

PS: I found it like this, I may be able to try and break apart the flags into as many boolean columns but it will be hard to get that going, so unless you can give me very strong reasons to do that please don't suggest it as an option..

Upvotes: 0

Views: 150

Answers (1)

danisius
danisius

Reputation: 597

Index is not used in bitwise opertion. It is better to use this query if application logic allows you to calculate combinations

SELECT idRow, idThing,  morColumns FROM MyTable WHERE idUser = ? AND flags IN (combination1 ..combinationn);

Also It`s not a good idea to use boolean fields and index on them because poor selectivity of the index. But if you have to split the data in bool fields you can use partial index on true fields.

Upvotes: 1

Related Questions