Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Please run SHOW CREATE TABLE tablename\G on that table and post it in the question. – RolandoMySQLDBA Oct 24 '13 at 21:15
Unfortunately I cannot as the design is proprietary. – Mike Purcell Oct 24 '13 at 21:18
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. – David Spillett Oct 24 '13 at 21:34
Uhhh it's not so much the data contained therein, it's the schema, hence the 'design'. – Mike Purcell Oct 24 '13 at 21:41
@MikePurcell, Actually the answer depends on whether you are optimizing for writes or optimizing for reads. – Pacerier Feb 1 '15 at 23:20
up vote 2 down vote accepted

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=...`` ANDenum4=...`, 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.

share|improve this answer
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? – Mike Purcell Oct 24 '13 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. – RolandoMySQLDBA Oct 24 '13 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. – RolandoMySQLDBA Oct 24 '13 at 21:39
Interesting. Thanks for the insight. I'll post back with a status update. – Mike Purcell Oct 24 '13 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. – Mike Purcell Oct 25 '13 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.

share|improve this answer
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. – Mike Purcell Oct 25 '13 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 '15 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. – Michael - sqlbot Jun 24 '15 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 '15 at 16:29

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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