Benefits of MySQL Enum?

Question

Is there a performance advantage when using an enumeration in situations where there are only 5-10 different possible values ​​for a field? if there is nothing advantage?

Answer 1

There is a huge performance limitation when using ENUM for operations such as:

• Request a list of valid values ​​in ENUM , for example, to populate a drop-down menu. You must query the data type from INFORMATION_SCHEMA and parse the list from the returned BLOB field.

• Change the set of valid values. This requires the ALTER TABLE statement, which locks the table and can perform restructuring.

I am not a fan of MySQL ENUM . I prefer to use lookup tables. See also my answer to " How to handle enumerations without enumeration fields in the database? "

Answer 2

ENUM is represented internally by 1 or 2 bytes depending on the number of values. If the lines you are storing are more than 2 bytes and rarely change, then ENUM is the way to go. Comparison will be faster with enumeration, and they take up less disk space, which, in turn, can lead to faster search time.

The disadvantage is that the listings are less flexible when it comes to adding / removing values.

Answer 3

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

The result is that when using ENUM, it may seem a little less elegant in terms of design (if the ENUM value sometimes changes), a performance gain is obvious for large data sets. See his article for details. Do you agree?

Answer 4

No, see the comparison here.

The advantage lies in the readability of the code.