Take the 2-minute tour ×

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?

share|improve this question
add comment

4 Answers

up vote 27 down vote accepted

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?"

share|improve this answer
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 Cumbee Apr 20 '09 at 19:33
"my enum values ... would never change". Would love to get some stats on how many times that statement has been proved wrong. –  benmarks Jun 25 '12 at 16:31
While the bullet points listed are true, ENUM is still faster than JOINS, especially if you are ordering by that column. For columns like gender with set values that don't change, I prefer using ENUM. If there is even a remote possibility that you will need to add or remove values though, go with a JOIN or use a CHAR/VARCHAR/TINYINT and manage them on the application level. One other thing...MySQL doesn't store the actual value in the column, only an index (INT), so you might as well use the full text string to display to your users (ie Male instead of M) and save additional coding. ;-) –  Jabari Nov 30 '12 at 20:51
@Jabari, true, but you can use a foreign key to a natural key in the lookup table (that is, no auto-increment), and then you don't need to do a join. –  Bill Karwin Nov 30 '12 at 21:00
@Bill Very true, but why do so if you have a few values that will never change? I like to keep my databases & queries as lean as possible. Creating a whole new table to hold "Male", "Female", and "Unknown" is untidy (for lack of a better word). If it increases performance or the values may change at some point, then by all means create another table! Otherwise it's just bloat. –  Jabari Nov 30 '12 at 22:31
show 3 more comments

In this article http://fernandoipar.com/2009/03/09/using-the-enum-data-type-to-increase-performance/ Fernando looks into performances of Enum type for queries.

The result is that while using ENUM might seem a little less elegant from a design point of view (if your ENUM value are changing sometimes), the performance gain is obvious for large datasets. See his article for details. Do you agree?

share|improve this answer
add comment

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.

share|improve this answer
add comment

No, see a comparison here

The advantage lays in code readability.

share|improve this answer
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 Dec 2 '09 at 16:57
add comment

Your Answer


