MySQL Enum performance advantage?

by

There is a huge performance penalty to using ENUM for operations such as:

  • Query the list of permitted values in the ENUM, for instance to populate a drop-down menu. You have to query the data type from INFORMATION_SCHEMA, and parse the list out of a BLOB field returned.

  • Alter the set of permitted values. It requires an ALTER TABLE statement, which locks the table and may do a restructure.

I’m not a fan of MySQL’s ENUM. I prefer to use lookup tables. See also my answer to “How to handle enumerations without enum fields in a database?“

More Related Contents:

  1. MySQL ENUM type vs join tables
  2. How do I add more members to my ENUM-type column in MySQL?
  3. Create a Cumulative Sum Column in MySQL
  4. Equivalent of explode() to work with strings in MySQL
  5. 1064 error in CREATE TABLE … TYPE=MYISAM
  6. mysql select dynamic row values as column names, another column as value
  7. Access denied for user ‘root’@’localhost’ (using password: YES) (Mysql::Error)
  8. MySQL DELETE FROM with subquery as condition
  9. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  10. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  11. Simple Random Samples from a Sql database
  12. node.js mysql error: ECONNREFUSED
  13. Relational database design multiple user types
  14. Checking multiple columns for one value
  15. What is better in MYSQL count(*) or count(1)?
  16. #1273 – Unknown collation: ‘utf8mb4_unicode_ci’ cPanel
  17. Should I use MySQL blob field type?
  18. How to lock a single row
  19. MySQL Cannot drop index needed in a foreign key constraint
  20. MySQL Insert & Joins
  21. Does MySQL overwrite a column of same value on update?
  22. How to join two tables using a comma-separated-list in the join field
  23. mysql like performance boost
  24. MySQL selecting yesterday’s date
  25. Does MySQL included with MAMP not include a config file?
  26. How do I write an SP in phpMyAdmin (MySQL)?
  27. In MySQL what does “Overhead” mean, what is bad about it, and how to fix it?
  28. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?
  29. delete rows from multiple tables
  30. MySQL Multiple Joins in one query?

Leave a Comment