Home ยป Content

October Boston MySQL User Group Topic: Boolean Values and Bit Operators

Boston October MySQL User Group: see full event listings at:

http://mysql.meetup.com/137/calendar/5118339/

Tuesday, Oct. 10th at MIT, free pizza and soda (thanks to MySQL, AB and the MIT community). Please RSVP!!

To RSVP anonymously, please login to the Meetup site with the e-mail address "admin at sheeri dot com" and the password "guest".

Plenty of free parking (you can park in MIT lots after 3 pm); 1 block south of the Kendall Square T stop.

--------------

Most of the September Boston User Group was spent discussing an interesting problem with a large amount of data (5 million records). Basically, this data had about 40 boolean (or small set) fields that needed to be able to be searched against. Folks suggested:

1) Just leaving the table as is and using 1-character values
Pro: simple
Con: Indexes are bad for columns with low selectivity, searching will take a long time due to full table scans

2) Creating a "joining" table for each boolean value
Pro: Indexing for each boolean value can be used
Con: Complex -- lots of tables, lots of joins for search

3) Using BIT(1) values or BIT(2) values and matching up booleans
Pro: Simple
Con: Difficult to write the search query, keeping in mind the search terms given below.

The biggest issue is the accuracy of indexes vs. size/amount of tables and joins. The person with the original problem (Chris) and I are doing a joint presentation, with real data on those three cases to figure out which is the best for his situation.

What have other folks done for boolean values? Please be specific about the amount of data, and the performance. Remember that this situation involves a lot of data and a lot of boolean fields, and searching across any or all boolean/small set fields is a core function. As well, fields may be null, and searching may include:

For boolean:
search for 0
search for 1
search for 0 or 1 (any value set)
search for NULL (any value not set)
search for 0 or NULL
search for 1 or NULL

For small sets:
search for 'a' (single value match)
search for 'a','b', and 'c' (multiple values will match)
search for 'any value not null' (anything not null)
search for 'any value including null' (anything null)

Any ideas? I will do some quick research if there's another option that the September User Group did not come up with.

One other options is to use

One other options is to use an ENUM field. However, you'll still run into the same problems as when you use a BIT field. A nice advantage of this is when you retrieve the data, you can have it return T/F or True/False, depending on how you declare the field.

Another idea: what about a 40-bit SET field? With this, you can store all 40 boolean values in one field. It's a little trickier to update, however I think it would speed up search/retrieval performance a good bit.

  • By Casey (not verified) at 10/03/2006 - 15:58
  • reply