Emulate ENUM from MySQL


#1

Is there anything special I should know about if I’m having an ENUM column in MySQL? I saw some suggestions to transform to a integer value or put it in a fulltext field. A sql_attr_string wouldn’t work?


#2

The enum is received as a string, so nothing special from the search engine perspective.
You can:

  • put it in a string attribute, this allows you comparison, grouping, ordering by it and available in result set (if your index is big enough keep an eye on the current 4GB string limitation)
  • crc it and put in a integer attribute; you will need to crc at search the values ( or alternative you can keep a mapping somewhere to integer values for all enums); integers have the advantage of using less space and memory
  • index it as fulltext field. You can only search it in this case. This suits when you don’t need to do grouping or ordering. Or you can use it along with an attribute to speed up searches in cases filtering by it as attribute is not great. Mind that if you have values like ‘dark blue’ and ‘blue’ you should search with start/end modifiers (to get the exact field match), like @field ^blue$

Hope this helps