I won't deal with the case of a UNIQUE constraint.
An index is basically a list in numeric or alphabetic order which points back to an internal row number for a table. So even if the table isn't sorted by the index, the index is, and allows speed of access.
If you limit an index to say 6 characters, then the index isn't going to differentiate between Fullen and Fullenheimer because "heimer" is past 6 on the count. So, for this case mySQL will initially thing there are two fullens when you query:
select * from table where name = 'fullen'
but then will discover that the second is fullenheimer, and skip that one. A few microseconds more in time.
If you went shorter with the index, say 4 characters, then a search
select * from table where name = 'fuller'
would find the following in the INDEX:
fuller
fullerman
fullman
fullen
fullback
and then be whittled down before final output.
Here's the tradeoff:
A LONG index takes up mucho space on large tables or long fields
A SHORT index may not save you much time on large tables because the internal process still has X number of potential matches.
A index without specifying the length is (I think) going to always be fastest because only exact matches come up in processing.
Sam
