Andrés
•
30 June 2024
There was a moment in my applications when I stopped persisting the fields that I was going to define as enums in int
fields and started doing it in strings
. I made this decision because, on several occasions, I was asked for a database dump for analysis, and whenever they encountered a status
field (or any other using enum
) with values like 0, 1, or 2, they would end up asking me about its meaning. So, instead of using numerical values that lack meaning and context, I directly changed them to a string
that in itself has meaning and provides context.
But a few days ago, I reviewed the documentation again and found a phrase that made me question this:
Finally it’s also possible to use a string column to persist the enumerated value. Note that this will likely lead to slower database queries
Using it would result in slower database queries!
So, as a good programmer, concerned about the efficiency and performance of my applications 🤓, I questioned what I was doing and, before making any changes, I wanted to verify this. I created a simple application in Rails connected to MySQL with two models, one with an enum persisted in int
and another persisted as varchar
, each with 100,000 records. I closed all applications that could interfere and ran a benchmark:
user system total real
String Enum Count: 0.477732 0.052428 0.530160 ( 21.794783)
Integer Enum Count: 0.374897 0.030260 0.405157 ( 21.639400)
String Enum Paginated Index: 0.351621 0.017249 0.368870 ( 0.639043)
Integer Enum Paginated Index: 0.317277 0.022936 0.340213 ( 0.524883)
String Enum Single Record Fetch: 0.294010 0.031218 0.325228 ( 0.489015)
Integer Enum Single Record Fetch: 0.297743 0.015502 0.313245 ( 0.497845)
All the results were very similar, with small variations in the time taken for 1,000 simple query executions. If I understand correctly, we could take the case of count
queries. They had a difference of 0.155383s, or approximately 0.1ms per query in favor of int
. But for a select
with limit(1)
, the string
won. So I wonder, will an enum
persisted as a string
really result in slower queries?
Well, whether it’s slower or not, I believe that in the average application, the difference will be in milliseconds and won’t be that significant. Therefore, my initial reason for starting to use enums
persisted as strings
remains the most important. And I’m not the only one with a reason for doing this; these two questions on Stack Overflow were looking for something similar several years ago:
What do you think? Did you know that an enum
can be persisted as a string
?