Optimizing Schema and Data Types - High Performance MySQL - page 124

You can work around this by specifying ENUM members in the order in which you want
them to sort. You can also use FIELD() to specify a sort order explicitly in your queries,
but this prevents MySQL from using the index for sorting:
mysql> SELECT e FROM enum_test ORDER BY FIELD(e, 'apple', 'dog', 'fish');
+-------+
| e |
+-------+
| apple |
| dog |
| fish |
+-------+
If we'd defined the values in alphabetical order, we wouldn't have needed to do that.
The biggest downside of ENUM is that the list of strings is fixed, and adding or removing
strings requires the use of ALTER TABLE . Thus, it might not be a good idea to use ENUM
as a string data type when the list of allowed string values is likely to change arbitrarily
in the future, unless it's acceptable to add them at the end of the list, which can be done
without a full rebuild of the table in MySQL 5.1.
Because MySQL stores each value as an integer and has to do a lookup to convert it to
its string representation, ENUM columns have some overhead. This is usually offset by
their smaller size, but not always. In particular, it can be slower to join a CHAR or
VARCHAR column to an ENUM column than to another CHAR or VARCHAR column.
To illustrate, we benchmarked how quickly MySQL performs such a join on a table in
one of our applications. The table has a fairly wide primary key:
CREATE TABLE webservicecalls (
day date NOT NULL,
account smallint NOT NULL,
service varchar(10) NOT NULL,
method varchar(50) NOT NULL,
calls int NOT NULL,
items int NOT NULL,
time float NOT NULL,
cost decimal(9,5) NOT NULL,
updated datetime,
PRIMARY KEY (day, account, service, method)
) ENGINE=InnoDB;
The table contains about 110,000 rows and is only about 10 MB, so it fits entirely in
memory. The service column contains 5 distinct values with an average length of 4
characters, and the method column contains 71 values with an average length of 20
characters.
We made a copy of this table and converted the service and method columns to ENUM ,
as follows:
CREATE TABLE webservicecalls_enum (
... omitted ...
service ENUM(...values omitted...) NOT NULL,
method ENUM(...values omitted...) NOT NULL,