Menu
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?
971
ENUM is great for data that you know will fall within a static set. If you are using Mysql 5+, storage is almost always better with an ENUM type for data in a static set, as the official MySQL reference shows. Not to mention that the data is readable, and you have an extra layer of validation.
The main benefit of enum is that constants can be referred to in a consistent, expressive and type safe way. Readability is of-course the topmost advantage of using the enumeration. Another advantage is that enumerated constants are generated automatically by the compiler.
Enums are not easily maintained, and lock the database into a version of the world when the database was designed. When using a text string to compare within code it is possible to misspell it and the language will not warn you. Enums are usually ordered in sorts in the order in which they were created.
When ENUM type has a long list of values. ENUM types should not be used if you cannot limit a set of possible values to a few elements.
ENUMs are represented internally by 1 or 2 bytes, depending on the number of values. 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.
33
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 from INFORMATION_SCHEMA, and parse the list out of a BLOB field returned.
Alter the set of permitted values. It requires an ALTER TABLE statement, 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?"
134
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
