Flags in a single column

[NotionCommotion]

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 ...;

[kicken]

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