Index for ENUM datatypes

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.

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.