MySQL Forums
Forum List  »  Performance

are index scans possible with bitwise comparison
Posted by: Gary Byatt
Date: July 21, 2005 12:38AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
are index scans possible with bitwise comparison
9608
July 21, 2005 12:38AM


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.