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?

  • Please run SHOW CREATE TABLE tablename\G on that table and post it in the question. Oct 24, 2013 at 21:15
  • Unfortunately I cannot as the design is proprietary. Oct 24, 2013 at 21:18
  • 1
    If the column names truly contain significant revealing information, just sanitise them and you can give Rolando the info he requested in order to give you free help, without ruining your business model. Oct 24, 2013 at 21:34
  • Uhhh it's not so much the data contained therein, it's the schema, hence the 'design'. Oct 24, 2013 at 21:41
  • @MikePurcell, Actually the answer depends on whether you are optimizing for writes or optimizing for reads.
    – Pacerier
    Feb 1, 2015 at 23:20

2 Answers 2


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:

    myenum INT NOT NULL,
    PRIMARY KEY (id)

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?


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

  • Interesting, didn't even consider a partition. So a partition like this would be faster than adding an index? What if there were multiple enum cols? Oct 24, 2013 at 21:32
  • If you have multiple enum columns, you should partition by the enum column that has the highest cardinality. That produces the smallest possible table within each partition. Oct 24, 2013 at 21:37
  • This would be better than indexing a low cardinality column. Given such an index, the MySQL Query Optimizer may never want to use it. Oct 24, 2013 at 21:39
  • Interesting. Thanks for the insight. I'll post back with a status update. Oct 24, 2013 at 21:47
  • I ended up fixing the lower layer code to accept enums, but convert to varchars, so now I can add indices. Thanks again, as I learned something new. Oct 25, 2013 at 0:27

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.

  • Nice. Appreciate the write-up. I actually ended up converting the enums to varchars, then added the indices. Took some refactoring on the lower level api, but seems to work well. Oct 25, 2013 at 4:20
  • Seems like a pretty misleading example. How often do people query against a multi million row table where the desired result is half the table? Add another more typical query with higher cardinality and does the optimizer choose to use that index? Your answer implies that indexes have no cost, when in fact they eat up space which is detrimental to the effectiveness of result set caching.
    – gview
    Jun 24, 2015 at 20:11
  • @gview There is no intention to imply that indexes are somehow without cost. Indexes, of course, require space to exist and resources to maintain. The example is an attempt to dispel the pervasive myth that low cardinality indexes in MySQL are not worth their cost because the optimizer won't use them... and for exactly that reason, it is a deliberately extreme example, with an index "less useful" than OP's question contemplates. Yes, indexes consume space in the InnoDB buffer pool, but that memory is not shared with the query cache, which is the only place MySQL caches actual result sets. Jun 24, 2015 at 22:43
  • I probably should have written "innodb block cache" rather than result set cache, but the idea is the same. The smaller the dataset, and less indexes involved the faster the db performs. This is why people avoid low cardinality indexes -- because they eat up valuable storage and are frequently not used. I don't disagree with the gist of your post, but it could easily be interpreted to mean "create low cardinality indexes -- just in case".
    – gview
    Jun 25, 2015 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.