Performance of SET versus Bitflags?

Anon

I have a web registration form which has a series of checkboxes on it (less than 64).

I have read the check article on phpbuilder and was wondering what is the best solution to store the checkbox data. Ideally, I would like to have something less complicated than
having serveral tables devoted to this.

So my question is this: performance-wise, how does the MySQL SET type compared to a bigINT bitflag implementation? Can indexes be used efficiently on either one of them? Or is there a better solution?

Thanks!

[deleted]

As you compare SET to bitflag, I guess you want to store the state of all the checkboxes in one record.

Simple math suggests that if you have 64 checkboxes, you have 2⁶⁴ possible combinations of checkboxes.
A SET type can only have 64 members, so you'd need a few (2.88E17) columns to have enough members.

BIGINT is 63bit signed, and the manual warns not to use unsigned values because the 64th bit might be mis-interpreted.

Another way to store the data would be to create one big table and store the data of each checkbox in it, along with data about who the record belongs to (userid etc).
That does give you one heck of a lot of records, but they are perfectly indexable, so speed is hardly an issue.

Anon

I am slightly confused by your comment:

"A SET type can only have 64 members, so you'd need a few (2.88E17) columns to have enough members."

I thought that the SET type only needs one column and takes up only 8 bytes (same as a BigINT)?

I have heard that it would be hard to uses indexes on SET and bitflags. Is this true?

I am currently using a single table with 22 columns to hold 22 checkboxes. But wouldn't be much more space efficient to use only one column to store ll the checkbox data?

If so, than which data type is better, SET or bitflags?

Thanks very much!

[deleted]

1) oopsy, I confused SET with ENUM (I'm working too hard)
with 64 or less checkboxes it could work, but getting the data back out is another matter

2) Space-efficiency is just about the least of your worries. (something like 2-3 bytes per value).
Speed is a much bigger issue. Inserting 22 values into a 22 column record may take some time, but processing 22 checkboxvalues into one value may take just as long.
And, when you get the data back out, how easy is it to convert the data back into something usable?

kparker

I have heard that it would be hard to uses indexes on SET and bitflags.
Is this true?

No kidding! What do you think they can do, create a separate index for each bit position in the column? 64 indices, think what what would do for the insertion speed! :-)