Using bitwise on a large number in mysql

Question

I have a need to store a provider name and the country(ies) they are able to provide services in. There are 92 counties. Rather than store up to 92 rows, I'd like to store 2^91 so if they provide only in county 1 and 2, I'd store 3.

The problem I'm having is the largest number is 2475880078570760549798248448, which is way too big for the largest BigInt.

In the past when I've had smaller numbers of options I've been able to do something like....

SELECT * FROM tblWhatever WHERE my_col & 2;

If my_col had 2, 3, 6, etc. stored (anything with bit 2) it would be found.

I guess I'm not sure of 2 things... how to store AND how to query if stored in a way other than an INT.

Bill Karwin · Accepted Answer · 2013-01-02 20:37:24Z

1

You could use BINARY(13) as a datatype to store up to 94 bits. But MySQL bitwise operators only support BIGINT which is 64 bits.

So if you want to use bitwise operators, you'd have to store your county bitfield in two BIGINT columns (or perhaps one BIGINT and one INT), and in application code work out which of the two columns to search. That seems like it's awkward.

However, I'll point out a performance consideration: using bitwise operators for searching isn't very efficient. You can't make use of an index to do the search, so every query is be forced to perform a table-scan. As your data grows, this will become more and more costly.

It's the same problem as searching for a substring with LIKE '%word%', or searching for all dates with a given day of the month.

So I'd suggest storing each county in a separate row after all. You don't have to store 92 rows for each service provider -- you only have to store as many rows as the number of counties they service. The absence of a row indicates no service in that county.

answered Jan 2, 2013 at 20:37

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Don Over a year ago

Thanks Bill. I was just reading on the indexing issue, so you may have a good point I hadn't realized. There's an added part that I left out for the sake of clarity where a provider may provide multiple services, so storing the provider ID, with each service ID in each county makes the table even bigger... I can at least index these though...

Bill Karwin Over a year ago

Yes, you may end up with a many-to-many table between services, providers, and counties. But with proper indexes, lots of sites use MySQL efficiently even with millions of rows in a table.

Collectives™ on Stack Overflow

Using bitwise on a large number in mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related