Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
    
And joins on ENUM will be bad because MySQL needs to lookup index positions off the strings that you want to join. so yes indexing on ENUM is pretty useless.. –  Raymond Nijland Oct 20 '13 at 17:00
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.