Re: Index evaluation

[Drei Bit]

It gets stranger:

explain select id from t2 use index (ix_txt_id) where txt like 'Hall%';

/*
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | range | ix_txt_id     | ix_txt_id | 768     | NULL |   80 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
*/

explain select id from t2;
/*
+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
|  1 | SIMPLE      | t2    | index | NULL          | ix_id | 4       | NULL | 400000 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
*/

Why is the optimizer reluctant to use the Index only? At least with range queries it seems. Or is he just hiding it?

Cheers
3bit