MySQL Index Prefix for better performance

In MySQL you can optimize a query running against a table by choosing the correct index. Furthermore MySQL allows you to index strings as well (text, varchar, binary, etc..) in an efficient way by using part of the field length, known as prefix.

Suppose we have the following table which is being queried by a varchar(50) field called Name:

mysql> select * from users where name like 'J%';
+------+-----------------+---------+
| id   | name            | country |
+------+-----------------+---------+
|    1 | James Attard    |     100 |
|    2 | James Azzopardi |     100 |
|    3 | Jameson Gomez   |     200 |
+------+-----------------+---------+
3 rows in set (0.00 sec)

If I put a regular non prefixed index such as the following, the key size will be equal to the full column length (100):

ALTER TABLE users ADD INDEX (name);

However if you look closely to the resultset of the query you will notice that we have cardinality with a prefix of 8 bytes: "James A%". Thus if we choose a prefix length of 10 (to be on the safe side) we are going to improve the performance even more as the key will be shorter than the full column length:

ALTER TABLE users ADD INDEX (name(10));