Assume my db will have millions of records and frequent selects.
If I plan on have a field that can be one of multiple states (A, B, C or
All), is it better to store as ENUM or TINYINT (as a mask, such that A =
1, B = 2, C = 3 and if I know that, then All = 6, and none = 0). I'm
only talking SPEED here in SELECT queries, not the added benefit of
having a 'multiple enum' as it were.
Furthermore, assume a record has two fields that are both binary
(true/false) toggles. This could be represented as:
Field_A enum("true", "false"),
Field_B enum("true", "false")
Field_AB enum("A", "B", "Both", "None")
# using the mask idea from above
Field_AB tinyint(1) unsigned
Is there any benefit to using one method over the other (again,
speedwise only)? My gut tells me the last is better, albeit a bit more
confusing to those who don't know what the number represents, but I'm
not sure if the query is optimized somehow before getting processed with
an ENUM down to numbers anyways.
Another thing, say I have
Field enum("true", "false")
And later ALTER TABLE to be
Field enum("on", "off")
Will all the records 'update' to the right name automatically, or will
they all still show 'true'/'false'?
Lastly, should I be using a "SET" instead of "ENUM" for this?
|• Benefits of using ENUM vs. a TINYINT "mask"?||Daevid Vincent||29 Oct|