Take the 2-minute tour ×
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.

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

2 Answers 2

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:

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=...`` 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?

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

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

Your Answer

 
discard

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.