I have a string column that I want to be part of a multi-column index in a large table in MySQL. There can be only a few different values in this column. If I knew these values in advance, I could make this column integer and a foreign key to another small table that lists all possible values. But I don't know them in advance.

Does it make sense to index on a string column: i.e. does MySQL somehow avoid repetition of the index column value for each row when storing internally?


There are 3 choices. VARCHAR is one of them; probably the 'worst'.

You suggested a second one -- an extra table.

The third is: ENUM('one_option', 'another', ...). It will take only 1 byte (like TINYINT). You can directly use the strings that you are currently using with VARCHAR. If you ever need to add another option, ALTER TABLE will be 'instant' if you add the new option on the end of the list.

It is useless to index low-cardinality columns; the optimizer will decide that a table scan is faster and ignore the index. However, a 'composite' index that includes such a column is often useful.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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