How does index perform with LIKE?

[Hoc Do Trung]

Hi all!

First, I'm sorry about my English.

Could someone help me to explain my problem?

I have a table that stores all my member's profile.
CREATE TABLE lbp.profile
(
member_id INT NOT NULL AUTO_INCREMENT,
display_name VARCHAR(255) NOT NULL,
INDEX profile__idx__display_name (display_name ASC)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

The problems come when I want to select members that has name start by a specified character.

My query is :
<code>
SELECT *
FROM lbp.profile
WHERE display_name like 'h%';
</code>

To check performance of the query, I do this :
<code>
EXPLAIN
SELECT *
FROM lbp.profile
WHERE display_name like 'h%';
</code>

The result show me that MySQL use 'ALL' type to search result, the posible key is key for display_name but not used. Very suprise, I try another query:
<code>
EXPLAIN
SELECT *
FROM lbp.profile
WHERE display_name like 'c%';
</code>

This time, MySQL use 'range' type to search result, the same posible key as before is used.

I do not why MySQL operate like that. So I do another action to check :
<code>
select substring(display_name, 1, 1) as abc, count(member_id) as `count`
from lbp.profile
group by abc
order by `count` desc
limit 5;
</code>

It's result :
+-----+-------+
| abc | count |
+-----+-------+
| T | 8974 |
| S | 7066 |
| h | 6828 |
| m | 6201 |
| C | 5813 |
+-----+-------+

Is it caused by big value at `count` colume?

Thanks.