are index scans possible with bitwise comparison
Background
I am using MySQL 4.1 which manages BIT(x) as TINYINT(1), a signed byte according to the docs
I have an InnoDB table `mytable` with an colum `mycol` of data type TINYINT(1) indexed with `myindex`
If I use a query like this:
SELECT * FROM `mytable` WHERE `mycol` & 1
the query works as I expect, matching the 1st bit in `mycol`, "& 2 matches" the 2nd bit etc.
EXPLAIN says possible_keys=null and key=null with "WHERE `mycol` & 1"
EXPLAIN says possible_keys=myindex and key=myindex with "WHERE `mycol` = 1"
From this it seems to me that it is not using the index with the bitwise comparison
This is still the case when changing the query to:
SELECT * FROM `mytable` FORCE INDEX (`myindex`) WHERE `mycol` & 1
Question
Is it possible to get index scans used with bitwise comparison in the WHERE clause?
Objective
To store 7 binnary values (possibly more later) efficiently and use index searches on them.
Using 7 separate TINYINT(1) fields seem very space inneficient, but I do need to use the values in search conditions and keep the speed of an index search
Thanks in advance
Gary
Subject
Views
Written By
Posted
are index scans possible with bitwise comparison
9688
July 21, 2005 12:38AM
6100
July 21, 2005 07:14AM
3393
July 22, 2005 02:23AM
4720
July 22, 2005 12:59PM
2768
July 25, 2005 08:00AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.