Jump to content
Sign in to follow this  
NotionCommotion

Flags in a single column

Recommended Posts

I haven't thought out the logic yet, but I will have several flags which will be used in a WHERE statement.  Other than maybe saving a little memory, is there any benefit of storing them as a bitfield in a single column.  Feel it is typically not worth the complication?  Regardless whether boolean/tinyint or a bitfield, if inserts are much less common than queries, should the columns be indexed?  Thanks

SELECT *
FROM table1 t1
INNER JOIN table2....
WHERE t1.f1=1 AND (t1.f2=0 OR t2.f1=1) AND ...;

Share this post


Link to post
Share on other sites

You could use the BIT for your columns. You'd still have separate columns to make your queries easier to write/understand but mysql would be able to pack multiple columns into a single byte for storage.

 

That said I've always just gone for a TINYINT for flag fields.

 

 

As for indexing it's generally only worthwhile if the flag state your interested in matches a relatively small number of the total rows. For example if your flag was deleted=1/0 and only 10% of the rows were deleted=0

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.