Index for ENUM datatypes

user_7378995 0
Viewed 10860

The text book tells me that it is not recommended to use index for enumerated datatypes. But it didn't tell me why. Should I use index for ENUM? The book also tells me that we should index column which we use in WHERE clause. I always use ENUM in WHERE part of my query and it should be indexed according to the book. And it also says not to index enumerated datatypes. Now what should I do?

Edit:

I think I made a mistake while asking, I just read the same book again and I think I got a misunderstanding while reading, the book didn't explicitly said we should not use index for ENUM but it said that we should not use index for columns that have very limited range of values such as yes/no, 0/1 etc. And the thing I grabbed from the book is that such columns are of ENUM types.

mysql
4 Answers

I just want to share my personal experience with an index on enums. I had a really slow query and found this while googling, which kind of discouraged me. But eventually I tried adding an index to my enum column anyhow.

My query was this:

SELECT * FROM my_table
WHERE my_enum IN ('a', 'b')
ORDER BY id DESC
LIMIT 0, 100;

The id column is the primary key. I have 25.000 rows in my_table. There are 4 possible values for my_enum.

Without an index on my_enum, the query took around 50 seconds to complete. With an index it takes 0.015.

This was on a 12 core Xeon Gold MySQL 8.0 server.

user_5611390

The enum data type is simply stored as a number (the position of the list item value within the list):

The strings you specify as input values are automatically encoded as numbers.

Thus, an enum field can be indexed just as any other numeric fields.

user_53899970

The reason we do not want to index a column with a small number of possible values is because of the nature of index itself. The common data structure of index is a balanced-tree with leaf node as linked list, which only supports fast lookup when variety of the values is huge. Otherwise, all the redundant values will be stored in a linked list which is not quite different from scanning the whole table, and sometimes it would be even slower if it needs to fetch the rows one by one from the table.

user_76917880

I was unsure myself so I did a little experiement. Following the MySQL Docs

I created a dummy table shirts and ran queries with index and without index on enum column size.

enter image description here

Table as roughly 2 million records enter image description here

Without Index

enter image description here

With Index

enter image description here

Conclusion

Query times didn't change significantly for me.

user_119532160
Related