MySQL Enum performance advantage?


Mysql Problem Overview

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?

Mysql Solutions

Solution 1 - Mysql

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

Solution 2 - Mysql

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.

Solution 3 - Mysql

In this article 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.

> Well, it's quite obvious that while using ENUM might seem a little less elegant from a design point of view (what happens if I use Types in more than just one table? What if I want to add a type? I have to alter the table!), the performance benefits, if I'm going to be handling large quantities of data (and my tests have been with small amounts, but I don't have a server, just a very humble notebook) might be worthwhile.

See his article for details. Do you agree?

Solution 4 - Mysql

No, see a comparison here

The advantage lays in code readability.


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questiongsueagle2008View Question on Stackoverflow
Solution 1 - MysqlBill KarwinView Answer on Stackoverflow
Solution 2 - MysqlJohn DouthatView Answer on Stackoverflow
Solution 3 - MysqlGuillaume GendreView Answer on Stackoverflow
Solution 4 - MysqlPaul GrigorutaView Answer on Stackoverflow