I wouldn’t follow this article. ENUMs are really only useful when you have a delineated set of data that will never change. That rarely happens plus alter enums is really costly on a large production table, more so than rebuilding an index. I would never use them for STI or polymorphics associations as the author suggests because the types often change in the lifetime of an application. The performance benefit over a varchar field that is indexed is really on the order of few percent.

Why would you be doing joins on an STI or polymorphic type column?

@wbailey, You bring up a good point and I wouldn’t advise this for use in early development, but in our production application this change was well worth price for the increased performance we got from our database machines where we were previously using indexed varchars.

ALTER TABLE is definitely expensive because in MySQL it makes a full copy (as I mentioned in the article), but with several database hosts they can be pulled and rolled one at a time and continue to replicate. We were able to make the change with 6 hosts on an 20G+ database on some of our largest tables without needing downtime. We also saw larger performance benefits than just a few percent, somewhere near 10.

On our database layer that is