MySQL - Add index to low cardinality text column?

Question

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?

Answer 1

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.

Answer 2

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> 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> 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.