List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 29 2002 9:05am
Subject:Benefits of using ENUM vs. a TINYINT "mask"?
View as plain text  
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")
OR
	Field_AB enum("A", "B", "Both", "None")
OR 
	# 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?

Daevid.

Thread
Benefits of using ENUM vs. a TINYINT "mask"?Daevid Vincent29 Oct