I have a table storing a bitmap of 336 bits, and I use 6 BIGINT to store it. The table is like

CREATE TABLE DATA_BITMAP (
    ID INT NOT NULL PRIMARY KEY,
    FIELD1 BIGINT, FIELD2 BIGINT, FIELD3 BIGINT,
    FIELD4 BIGINT, FIELD5 BIGINT, FIELD6 BIGINT
)

I need to implement a function check(idx) to check the idx-th bits of the bitmap, like check(331) and check(123).

The following query is what I need

SELECT * FROM DATA_BITMAP bm JOIN OTHER o 
ON bm.id = o.some_key 
WHERE check(o.data_index) > 0

The target bit is stored in another table, so for each row, the field that needs to be checked may be different.

I think I can write a SQL function that does this

FUNCTION check(idx) {
    if(idx < 64) {
         return field1 & (1 << idx);
    } 
    if(idx >=64 AND idx < 128) {
         return field2 & (1 << (idx-64));
    }
    ......
}

But I would like to ask is there a better way of doing this? I can modify the table structure if necessary. Thanks!

CodePudding user response：

It doesn't matter how you code your check() function. Any use of a function for doing a lookup like that cannot use an index, so it's going to be forced to do a table-scan in any case.

This is pretty common when storing bitfields, if you want to do lookups for a specific bit. The only way to make this optimized so it doesn't do a table-scan is to restructure the data so you store one bit per row.

CREATE TABLE DATA_BITMAP (
    ID INT NOT NULL,
    BIT_NUM SMALLINT NOT NULL,
    PRIMARY KEY (ID, BIT_NUM)
)

Store a row if the bit is set, and no row if the bit is not set. Then you can look up a given bit like the following, and it'll use the primary key index:

SELECT COUNT(*) AS IS_SET FROM DATA_BITMAP WHERE ID=? AND BIT_NUM=?

The COUNT() will return 0 or 1 in this case, since it's a lookup into a unique key. There can only be 0 or 1 matching row.