I've an email column during my table the industry VARCHAR(255). Most emails would simply be 40-50 figures lengthy, and also the relaxation from the figures exist just there for any rare situation. Therefore, I wish to only index the very first 50 figures from the email column.

I have attempted to get it done with this particular query:

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

However, that produces 2 indexes, one named email and also the other email_2. The very first index doesn't have length limits, the 2nd one gets the 50 character limit.

Why? Can there be any workaround or have i got no option but to index the entire column?

Why bother indexing just the first 50 figures? MySQL is going to be wise enough to simply use space within the index for that actual data. I.e., it is not likely to allocate 255 figures of space for every entry. Thus, there's pointless to not simply index the whole column because it is.