MySQL - Add index to low cardinality text column?

I'm working on project where an enum column is being converted into a text column (I cannot change this). The cardinality of the column is low (7 unique values). Would I gain a performance increase by adding a 10-15 char index, or is the cardinality low enough where the index would result in diminishing returns?

The size or datatype of the column is irrelevant. It is the unique values that matter. If you only have 7 unique values that means 14.286% of the rows have to be considered.

Instead of giving the MySQL Query Optimizer the stress of figuring out that out, you should partitioning the table by hash:

CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    ...
    ...
    myenum INT NOT NULL,
    PRIMARY KEY (id)
)
PARTITION BY HASH( myenum )
PARTITIONS 7;

No need to have the myenum in any indexes. Leave it to the MySQL Query Optimizer to search the correct partition should any SELECT query have a WHERE clause that includes AND myenum = ....

If you ever have to increase the number of unique values, you will have to increase the number of partitions.
Give it a Try !!!
UPDATE 2013-10-24 17:57

As I said in the comments, you should partition by the enum with the highest cardinality.

What about the other enums? DO NOT INDEX THE ENUM BY THEMSELVES !!!

If your SELECT queries include WHERE enum2... AND enum3=... AND enum4=..., you should think about making compound indexes of enums.

For example, if you have enum2, enum3, and enum4, you could make compound indexes like these:

ALTER TABLE mytable ADD INDEX (enum2,enum3,enum4);
ALTER TABLE mytable ADD INDEX (enum3,enum4);

Which order should you choose?

• CARDINALITY(enum2) > CARDINALITY(enum3)

• CARDINALITY(enum3) > CARDINALITY(enum4)

CAVEAT : Again, I like to emphasize, if you partition by enum1, there is no need to index on enum1`.

StackExchange Database Administrators Q#52156, answer score: 4