benefits of using ENUM

[misc00500]

What are the benefits of using ENUM over small reference tables ?  IE.

 

property (

 

property_status    ENUM('open', 'closed')

 

);

 

vs.

 

property_status (

 

  id            int primary key,

  type        char(6)

)

 

insert into property_status(type) values ('open'), ('closed')

 

 

As I understand it, Mysql seems to be the only database system to support ENUM.  And it sort of violates normalization rules ( atomic values ).  Other than the benefit of easier to query and less JOIN statements, is there a performance gain to be had by using ENUM ?

[Wildbug]

Use of ENUM should save alot of space given a column that can only have a certain values.

 

ENUM [storage requirements]...1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)

 

So, in your first example, every row would need 1 byte to store that column.  In the second example, with CHAR(6), each row would take six bytes to store that information.

 

(It seems like a colunmn could even use less -- as little as one bit for a no-null, two-value column -- but it doesn't appear it is implemented as such.)

 

So there's a storage benefit, and there may be a performance benefit on searching, but I don't know for sure.

[Barand]

 

As I understand it, Mysql seems to be the only database system to support ENUM.  And it sort of violates normalization rules ( atomic values ).  Other than the benefit of easier to query and less JOIN statements, is there a performance gain to be had by using ENUM ?

 

It's either "open" or "closed" - still a single value  except it's now stored as 0 or 1.

 

SETS, on the other hand, do go against the grain when it comes to normalizing repeating data items.

[fenway]

ENUM obviously saves a table JOIN every single time, but is very inflexible.