Creating a MySQL index on a Varchar always makes 2 indexes?

Question

I have an email column in my table which is a VARCHAR(255). Most emails would only be 40-50 characters long, and the rest of the characters are there just there for a rare case. Therefore, I want to only index the first 50 characters of the email column.

I've tried to do it with this query:

ALTER TABLE  `users` ADD INDEX (email(50) )

However, that creates 2 indexes, one named email and the other email_2. The first index has no length limits, the second one has got the 50 character limit.

Why is this? Is there any workaround or do I have no option but to index the full column?

Answer 1

Why bother indexing only the first 50 characters? MySQL will be smart enough to only use space in the index for the actual data. I.e., it isn't going to allocate 255 characters of space for each entry. Thus, there is no reason not to simply index the entire column as it is.