Is there a performance advantage to using enum in situations where there are only 5-10 different possible values for a field? if not what is the advantage?
+1
A:
No, see a comparison here
The advantage lays in code readability.
rpSetzer
2009-04-19 22:55:07
According to the article you linked there IS a performance advantage to using ENUM as long as you are not altering possible states.
Dmitri Farkov
2009-12-02 16:57:36
+2
A:
ENUMs are represented internally by a 16-bit unsigned integer. If the strings you're storing are larger than 2 bytes and rarely change, then an ENUM is the way to go. Comparison will be faster with an enum and they take up less space on disk, which in turn can lead to faster seek times.
The downside is that enums are less flexible when it comes to adding/removing values.
John Douthat
2009-04-19 22:58:30
+6
A:
There is a huge performance penalty to using ENUM for operations such as:
Query the list of permitted values in the
ENUM, for instance to populate a drop-down menu. You have to query the data type fromINFORMATION_SCHEMA, and parse the list out of a BLOB field returned.Alter the set of permitted values. It requires an
ALTER TABLEstatement, which locks the table and may do a restructure.
I'm not a fan of MySQL's ENUM. I prefer to use lookup tables. See also my answer to "How to handle enumerations without enum fields in a database?"
Bill Karwin
2009-04-19 23:14:17
my enum values would be things like demographics (W,B,A,H,O,U) Gender(M,F,U) and Party(R,D,I,U) those would never change. so they can always be hard coded into my application logic. So Querying for dropdown values, and altering the structure are not that big of a factor.
Eric
2009-04-20 19:33:38