Index for ENUM datatypes

Question

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.

Answer 1

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.

Answer 2

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.