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?
I could hardly disagree any more than I already do with the accepted answer, for two reasons.

First, all the talk about the optimizer not using low cardinality indexes is overblown.  Its true that the optimizer may not prefer it, and it's true that the optimizer may sometimes choose to disregard it, but I have seen posts suggesting that if more than "x" percent of the rows match an index, it wont be used.  And that's absolutely not true.

I'm sitting in front of a table with over a million rows in it.  It has an indexed enum column, along with a number of other indexes, but I'm showing that index below.  Note the cardinality is 2.

mysql&gt; show indexes in xxxxxxx;
+---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xxxxxxx |          1 | target_xxx          |            1 | target_xxx          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


So, does the optimizer use this index?

mysql&gt; explain select * from xxxxxxx where target_xxx = 'default';
+----+-------------+---------+------+---------------+------------+---------+-------+---------+-----------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows    | Extra                 |
+----+-------------+---------+------+---------------+------------+---------+-------+---------+-----------------------+
|  1 | SIMPLE      | xxxxxxx | ref  | target_xxx    | target_xxx | 1       | const | 1269015 | Using index condition |
+----+-------------+---------+------+---------------+------------+---------+-------+---------+-----------------------+
1 row in set (0.23 sec)


Yes, it does.  It uses the index for rows that match, and it also uses the index to almost immediately tell me that no rows match if I used a value in the where clause that is not found anywhere in the table in that column.

The myth of low cardinality indexes not being useful or used... seriously needs to be discarded.  

Give the optimizer choices.  That isn't something you want to avoid.

Second, if you partition the table as discussed, then every query that does not reference that column in its where clause now has all 7 partitions to deal with (and 7 sets of indexes).  Unless there is something really significant and meaningful about this column that means you'll be interrogating it in most of your where clauses, partitioning on it doesn't seem like a particularly good plan.  

Partitioning is not a magic bullet.  

It is, however, a bullet of a different kind -- and it tends to point toward your foot unless appropriately used.