Can I force MySQL to use an index?

I created a table which has close to 800,000 records.

mysql> describe automation_search_test;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment | 
| filename | text    | YES  | MUL | NULL    |                | 
| site     | text    | YES  | MUL | NULL    |                | 
| script   | text    | YES  | MUL | NULL    |                | 
| station  | text    | YES  | MUL | NULL    |                | 
| result   | text    | YES  | MUL | NULL    |                | 
| failcode | text    | YES  | MUL | NULL    |                | 
| stbmodel | text    | YES  | MUL | NULL    |                | 
| rid      | text    | YES  | MUL | NULL    |                | 
| testdate | text    | YES  | MUL | NULL    |                | 
+----------+---------+------+-----+---------+----------------+

with an index on the filename column

mysql> show index from automation_search_test;
+------------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                  | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| automation_search_test |          0 | PRIMARY       |            1 | id          | A         |      767825 |     NULL | NULL   |      | BTREE      |         | 
| automation_search_test |          1 | ast_fname_idx |            1 | filename    | A         |      767825 |      255 | NULL   | YES  | BTREE      |         | 
+------------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

With a WHERE condition, a query against the filename column uses the index without problem.

However, a simple SELECT query against the filename column ignores the index

mysql>  explain select filename from automation_search_test;
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table                  | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | automation_search_test | ALL  | NULL          | NULL | NULL    | NULL | 7开发者_如何学Go67825 |       | 
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+

How can I force the use of this index??

---

Since SELECT without a WHERE condition returns all of the values in that column, there is nothing for the index to do.

That's a bit like asking why the index at the back of a textbook isn't used when I want to read the whole book.

---

It sounds as if you are looking for a covering index. A covering index (one that can satisfy the entire query without going to the table) only works if it contains the complete data. In your example, the index on filename includes up to 255 characters. If the actual file name were longer, it would not contain the entire data, so it is not a covering index for that query.

If filename had a type such as varchar(255), then it would use the index for the example query.