MySQL more than one INDEX key for the same column

Question

Is it right to have more than one INDEX key for the same column in MySQL database?

for example, id field indexed twice with different Keyname while phpmyadmin gives me a warning message:

More than one INDEX key was created for column id

I would like to know if that is ok, and if there are any effects or side-effect on my script or the server using this method?

I use this method for grouping columns for each index.

Answer 1

Indexing a single column twice is useless, slows down inserts and updates because now you have to (uselessly) maintain two indexes, and probably confuses the optimizer (if it doesn't actually break something). On the other hand, it's fine (and often useful) to have a column indexed alone and then also as part of one or more compound keys.

Answer 2

Theoretically it can be a good idea to have a reverse index on a column as well as the normal index. Not sure if its supported by MySQL though.

It depends what you are seraching for. If you are expecting the user to search for lastnames, and you store first and last names in the same column, then many searches will be of the form

LIKE %lastname

In that case, a normal index will not help much, because it builds the index from the beginning of the string. It will need to look through every record to see that it at some point doesn't contain the search string. A reverse index, will be useful, because it indexes from the back and foward. Using double indexes would speed up this particular kind of search.

With wildcards at both the beginning and the end.