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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

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.