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));
No comments:
Post a Comment