Using String enums in MySQL - performance question

Question

I currently use enums as TINYINTs in MySQL database. My tables have a few million rows in them. We then map the enums from TINYINT to Strings elsewhere.

I am considering storing these enums as Strings. If the Strings are indexed properly, does any one know how MySQL performs when querying on String enums instead of TINYINT enums?

Appreciate the help. Thanks.

Placida

Can you clarify if you are using MySQL's ENUM (dev.mysql.com/doc/refman/5.0/en/enum.html) data-type or not? – Skiagraph

Answer 1

Enums seem to have a slight edge, according to these benchmarks.

Spode

Answer 2

Enums are stored as if there was a lookup table, so only a numerical reference is stored on each row, with a lookup table elsewhere. Technically, matching the integer value of an enum is as fast as matching an integer as the two are identical, and matching a string value is marginally slower (in all instances bar one), simply because only one more "search" is done, through the lookup table to find the integer value, which is then used to perform the lookup.

That one instance is if you search for a value that doesn't exist, try matching the numerical value of enum for a value that doesn't exist, and it will still search through all the data, but try matching a string value that doesn't exist, and a smaller search through the lookup table is done, returning no match before any data is searched.

Don't use enums for "yes/no" or "male/female" answers, as the numerical data and lookup table take up far more room than a "bit" value (0 or 1). Also, don't use numerical strings in the enum definition, e.g. enum("1","0") as this is confusing for anyone looking at the code and the query (because the lookup table stored is [0 => "1", 1 => "0"].

Reeding

Answer 3

(Assuming you are using TINYINT and have a lookup table) TINYINT is faster, but you might end up seeing some performance hits because of joins.

My advice, if your enumeration values wont change (eg - Male, Female types, yes no types of enums), use MYSQL ENUM field instead. It gets stored using 1 byte (if enum values are less than 255), and doesnt need joins.

But be warned and read up all the pros and cons of ENUM data types before taking the plunge.

Leffen

Answer 4

TINYINT (or INTEGER for that purpose) is indexed faster and gives better performance in SORT or SELECTs than VARCHAR or CHAR.

So, to answer you, yeah Strings are indexed properly but the performance might go down. I can't tell how significant that would be.

---

Edit 1 more info

Seems like smaller VARCHAR and INTEGER has negligible performance difference in indexing. See here http://forums.mysql.com/read.php?115,251611,252006#msg-252006

Skiagraph