When I use an ENUM column in a table the ORDER BY uses the ordinal of the ENUM not its value. I wasn't aware of that.
So my question is what are the subtle issues when an ENUM is part of an index definition? Is it an antipattern or bad idea?

  • ENUM can be indeed an antipattern.. if you need to support an new type you must ALTER TABLE to support that new type.. most off the times this will lock out the table for some time.. note MYSQL 5.6 has live ALTER TABLE capabilities what doesn't lock the table. – Raymond Nijland Oct 20 '13 at 16:41
  • @RaymondNijland:Yes I know that.I am asking only in relation to indexing – Cratylus Oct 20 '13 at 16:43
  • i readed the question wrong but you already know that.. well isn't easy to query distinct values from an ENUM.. maybe the MySQL optimizer could have trouble with it and choose not to use that index.. – Raymond Nijland Oct 20 '13 at 16:53
  • And well if your need to ORDER BY... you need to do ORDER BY FIELD(enum_field, values) that will use an filesort (quicksort) pass for sure – Raymond Nijland Oct 20 '13 at 16:57
  • 1
    That's an extreme overstatement. The optimizer resolves the string equivalents into integers. Indexing an enum is extremely valuable for equality ref lookups against an enum and grouping by an enum, just not so much for ordering. – Michael - sqlbot Oct 20 '13 at 23:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.