tag:blogger.com,1999:blog-18337119.post113824800915254294..comments2024-01-22T07:09:30.574-05:00Comments on MySQL Consulting and NoSQL Consulting: MySQL DBA: Does using 'Enum' affects the application performance?Unknownnoreply@blogger.comBlogger7125tag:blogger.com,1999:blog-18337119.post-76674034769729682762008-11-26T20:15:00.000-05:002008-11-26T20:15:00.000-05:00Another small thing I realized with my DB:I have t...Another small thing I realized with my DB:<BR/><BR/>I have teams, which can be male, female or mixed.<BR/><BR/>I have persons, who can only be female or male.<BR/><BR/>Having the ability to use two different enums<BR/><BR/>teams: ENUM('female', 'male', 'mixed')<BR/>persons: ENUM('female', 'male')<BR/><BR/>won't allow 'mixed' to be set for persons, if you'd alternatively create one table for all three gender types... and again, it saves a join.<BR/><BR/>Plus: Your DB model becomes less complex and cluttered. If I imagine adding another table for my 6+ enums, that'll increase the complexity a lot and I'd have to move around the boxes all new...Kawuhttps://www.blogger.com/profile/13494634864694948837noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-1138500907955179602006-01-28T21:15:00.000-05:002006-01-28T21:15:00.000-05:00Very true Roland.Looking back, I have used Enum ty...Very true Roland.<BR/><BR/>Looking back, I have used Enum type for a field only when I wanted to ensure that only a set of values would make it to the database.<BR/><BR/>I would be awesome to have MySQL support CHECK constraints. Like you, I would convert in a heart beat as this will be a giant leap in ensuring data integrity.<BR/><BR/>FrankFrankhttps://www.blogger.com/profile/04086666898806120300noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-1138498317311893022006-01-28T20:31:00.000-05:002006-01-28T20:31:00.000-05:00Just another note, totally beside the perfomance p...Just another note, totally beside the perfomance point...In MySQL Enums provide a natural, declarative means to restrict a domain to a fixed set of values, which is sometimes very useful (think column gender enum('female','male','not specified')) In fact, I would always use ENUM to solve such a problem even though you can use a trigger to check for this in standard SQL.<BR/><BR/>(On the other hand, if MySQL would support CHECK constraints, I'd probably revert (convert) to those.)rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-1138453199609525412006-01-28T07:59:00.000-05:002006-01-28T07:59:00.000-05:00You don't need to only benchmark, you need to main...You don't need to only benchmark, you need to maintain that app, you'll soon discover why enum are evil.pablojhttps://www.blogger.com/profile/03043517866113096024noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-1138296760182334062006-01-26T12:32:00.000-05:002006-01-26T12:32:00.000-05:00Thanks Roland and Mike for your comments.I agree w...Thanks Roland and Mike for your comments.<BR/><BR/>I agree with both of you.<BR/><BR/>Roland, you are right in pointing out that due to the smaller index size there will be "less bytes to search through."<BR/><BR/>Mike, yes, the best answer is to 'benchmark it.' <BR/><BR/>I was kind of hoping someone would have done it in the past. <BR/><BR/>I am sure the benchmark data would help many passionate fans of MySQL.<BR/><BR/>Thanks guys,<BR/><BR/>FrankFrankhttps://www.blogger.com/profile/04086666898806120300noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-1138287943113204312006-01-26T10:05:00.000-05:002006-01-26T10:05:00.000-05:00I hate answering performance questions with 'bench...I hate answering performance questions with 'benchmark it', but sometimes that is the real answer. On the one hand, an ENUM can save you a join, and joins can be costly depending on the curcumstances. On the other hand I haven't looked at the index efficiency of ENUM lately. <BR/><BR/>Might be time to break out some benchmarking tools and a good size data set.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-1138269152637653222006-01-26T04:52:00.000-05:002006-01-26T04:52:00.000-05:00My immediate reaction would be to say that convert...My immediate reaction would be to say that converting a string column to an enum could definitely improve performance for certain types of queries.<BR/><BR/>That's based on the idea that an index based on an enum column will usually be a lot smaller: less bytes to search through. <BR/><BR/>So, I expect the ENUM to be faster in cases where you'd have fairly long strings (at least, longer than than 4 bytes) and where to query would use simple equality comparison.<BR/><BR/>(I tried to test it, but I don't have enough data for a real test right now. Maybe tonight)rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.com