Menu
I have a query which looks like:
select count(*) from m1
WHERE (m1.`resource` & 1472 );
Although I have index on resource it doesn't use it. How can this be optimized to use on bitwise operation.
547
In case the query optimizer ignores the index, you can use the FORCE INDEX hint to instruct it to use the index instead. In this syntax, you put the FORCE INDEX clause after the FROM clause followed by a list of named indexes that the query optimizer must use.
A bitwise XOR is a binary operation that takes two bit patterns of equal length and performs the logical exclusive OR operation on each pair of corresponding bits. The result in each position is 1 if only one of the bits is 1, but will be 0 if both are 0 or both are 1.
Yes, MySQL uses your index to sort the information when the order is by the sorted column. Also, if you have indexes in all columns that you have added to the SELECT clause, MySQL will not load the data from the table itself, but from the index (which is faster).
I do not believe MySQL can be made to use indexes for bitwise operations.
There's some discussion of this in the MySQL Performance forum: http://forums.mysql.com/read.php?24,35318 ("Are index scans possible with bitwise comparison?") where a MySQL employee suggests a solution based on having a table with one row per (thing,set-bit) pair and doing a bunch of joins. I'd guess that how well this works will depend a lot on your particular application.
According to http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html indexes aren't any use for doing the same sort of operations on SET values (which are implemented with integers and bitwise operations). I'd have thought that if there were any clever index optimization for bitwise operations it would already have been applied to SETs.
156
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
